Controlling Excel from Access (Access 2000)

Topic

New Reply

Right now I’m grasping at straws to work-around my problem with reading a .dbf file from Access. So, I hit upon the solution of using Access to open the .dbf in Excel, then saving it to a .txt file, which I can than link to. I’m able to open Excel, but I can’t seem to save the worksheet as a different format. I’m using:

    objXLBook.SaveAs "c:my documents10289002.txt", "Text(Tab delimited)"

It is apparently the “Text(Tab delimited)” that is the stumbling block (I’ve also tried it with a space inserted after Text, and also with just “Text”). If I leave off this parameter altogether, it saves the worksheet in its original format to the file I specified. Does anyone know what the proper parameter is? I’m really desparate!

Reply | Quote

Replies

I think your syntax should look something like:

ActiveWorkbook.SaveAs Filename:= _
“c:my documents10289002.txt”, FileFormat:=xlText _
, CreateBackup:=False

but I think you should be able to import the .dbf file without problems.

Reply | Quote

My main computer didn’t have the Help file for the Excel object library, and until just a moment ago I didn’t think to look for it on my laptop! So thanks.

And yes, I think I should be able to import the .dbf file, but I can’t on that one computer, and I don’t know why.

Reply | Quote

I don’t know if this works with your .dbf files, with an Access to Excel export via automation the following syntax will save the file as tab-delimited text:

wb.SaveAs strPath & strFile, xlText 

where wb is a variable representing active workbook. I usually don’t save XL file in this format but in quick test seems to work.
HTH

Reply | Quote

Thanks. As I mentioned to Wendell, I couldn’t figure this out at first because the computer I was using didn’t have the Excel object library Help file, and I didn’t even know there were constants available for this. When importing in Access using Transfer Database method, you actually spell out the file type, so I was trying to do the same thing!

Reply | Quote

I checked the syntax the old-fashioned (lazy) way, recorded a “macro” in Excel & then checked to see what it gave me…

Reply | Quote

>>I checked the syntax the old-fashioned (lazy) way, recorded a “macro” in Excel & then checked to see what it gave me…<<

That was clever. I had just "assumed" the syntax would be the same. Silly me!

Reply | Quote