M
Macroman
Not sure if this is possible or within the correct posting, as I have been
unable to find anything in various web sites and forums.
I have got a file called "locata.csv" containing location attributes , house
number , road name , town etc.
A second file called "lookup.csv" created by a macro I wrote and activated
by a button.
This is what happens, a users of my workbook will have a list of lets say
2,000 different URN's, each URN referring to a location, and they wish to
look up certain attributes of these locations easily and quickly. There will
be duplicate URN's in the users dataset which they would have copied and
pasted onto an Excel worksheet from another source so the number of rows can
be as many as 20,000.
The user will then run my macro which will export there URN's to a file
named "lookup.csv"
I have inserted a database query containing two tables "lookup.csv" and
"locdata.csv" and created a join between the two URN's and the results
returned back to the Excel spreadsheet.
So far this has worked fantastically and I have no problems with that, I can
even (as administrator of the workbook) add data to the "locdata.csv" file
by appending to the file using a macro.
Now this is the problem I am having trouble with, I am sort of 50% confident
in SQL and know the syntax for deleting a records is DELETE from "a table"
WHERE URN = "1234". But how on earth can I implement this within my Excel
workbook.
At present this is my working solution.
List of URN's to delete listed on a worksheet.
"locdata.csv" copied as "bcklocdata.csv"
A macro then reads one line at a time from "bcklocdata.csv" the URN is
copied to cell ref A1
Using MATCH function I check if the URN is in the list of worksheet URN's to
be deleted.
If it is not (ie I get "#N/A") then write the line read from
"bcklocdata.csv" to a newly created file "locadata.csv"
If the URN is in the list skip past writing the line and goto the next
record from "bcklocdata.csv"
Of course you can see my dilemna having 180,000 plus records this process
does take time.
The solution some of you may be thinking is use MS Access , but at work I
dont have that luxury we are stuck with MS Excel 2K.
Nothing wrong with Excel I love it to bits.
So is it possible to have within my VB code, syntax that will allow me to
loop through a list of URN's from a worksheet and use the MS SQL syntax of
DELETE against the "locdata.csv" ?
many thanks
Macroman
unable to find anything in various web sites and forums.
I have got a file called "locata.csv" containing location attributes , house
number , road name , town etc.
A second file called "lookup.csv" created by a macro I wrote and activated
by a button.
This is what happens, a users of my workbook will have a list of lets say
2,000 different URN's, each URN referring to a location, and they wish to
look up certain attributes of these locations easily and quickly. There will
be duplicate URN's in the users dataset which they would have copied and
pasted onto an Excel worksheet from another source so the number of rows can
be as many as 20,000.
The user will then run my macro which will export there URN's to a file
named "lookup.csv"
I have inserted a database query containing two tables "lookup.csv" and
"locdata.csv" and created a join between the two URN's and the results
returned back to the Excel spreadsheet.
So far this has worked fantastically and I have no problems with that, I can
even (as administrator of the workbook) add data to the "locdata.csv" file
by appending to the file using a macro.
Now this is the problem I am having trouble with, I am sort of 50% confident
in SQL and know the syntax for deleting a records is DELETE from "a table"
WHERE URN = "1234". But how on earth can I implement this within my Excel
workbook.
At present this is my working solution.
List of URN's to delete listed on a worksheet.
"locdata.csv" copied as "bcklocdata.csv"
A macro then reads one line at a time from "bcklocdata.csv" the URN is
copied to cell ref A1
Using MATCH function I check if the URN is in the list of worksheet URN's to
be deleted.
If it is not (ie I get "#N/A") then write the line read from
"bcklocdata.csv" to a newly created file "locadata.csv"
If the URN is in the list skip past writing the line and goto the next
record from "bcklocdata.csv"
Of course you can see my dilemna having 180,000 plus records this process
does take time.
The solution some of you may be thinking is use MS Access , but at work I
dont have that luxury we are stuck with MS Excel 2K.
Nothing wrong with Excel I love it to bits.
So is it possible to have within my VB code, syntax that will allow me to
loop through a list of URN's from a worksheet and use the MS SQL syntax of
DELETE against the "locdata.csv" ?
many thanks
Macroman