Export to text with custom delimination

[edward]

Thank you in advance for reading my question...

I am a database programmer, my strength is not Excel, but here I am
with a new task, so I am coming to you for some guidance.

We receive an excel spreadsheet from an online company that collects
data for us. The spreadsheet has roughly 50 columns. Every time we get
the speadsheet (once a week or so), the columns are always in the same
order with new data.

I want to create a macro that goes through the columns and grabs the
data I want, and outputs it to a text file, deliminated within SQL
statements. As an example, imagine a spreadsheet that looks like this:
[image: http://www.1st-r8.com/working/images/excel111.gif]
could produce a text file that reads something like this

Insert into mytable (firstname, lastname, address1, city, state, zip)
values ('Steven', 'Smith', '1144 Hill Dr.', 'Glendale', 'CA',
'91208');
Insert into mytable (firstname, lastname, address1, city, state, zip)
values ('Mary', 'Bump', '2288 Mill Lane', 'Pasadena', 'CA', '91343');
etc.....


Notice how the macro would be written to skip over column C. I am very
skilled at writing scripts including VBS and Active Server pages. But,
I am new to programming in Microsoft Office Aps, especially Excel. So,
if you could point out some online articles or tutorials, or if you
have done somthing similar and can post your notes, I would be very
grateful.

Sincerely,

Edward Stoever
(e-mail address removed)

 

[Tim Williams]

dim iRow as long
dim sSQL
const NUMCOLS=50

irow=1
sSQL=""

do while activesheet.cells(iRow,1).value<>""

with activesheet.rows(iRow)

sSQL=sSQL & "Insert into mytable (firstname, lastname, address1,
city, state, zip) values ("

for x=1 to NUMCOLS
if x<>3 then
sSQL=sSQL & "'" & .cells(x).value & "'"
if x<> NUMCOLS then sSQL=sSQL & ", "
end if
next x

sSQL=sSQL & ");" & vbcrlf

end with

iRow=iRow+1
loop

'then write out sSQL to a file using FSO or the VB file access functions.

You haven't specified how you want to handle empty values/nulls etc, and if
you have many rows you will find that the string concatenation gets v.slow,
but this approach should work fine.


Tim.
saxifrax@pacbell[removethis].net

 

[edward]

Works like a charm.... THANK YOU!

Edward