Using Shell in VBA

[ghutche]

I am writing code to open a .csv file in access and then save this as an .xls file. I can open the file using shell but haven't been able to manipulate excel. I've tried the AppActive method but it doesn't seem to change the focus to the excel file. Here is the code I'm struggling with.



csvfile = Shell("excel.exe" & " " & TargetFile, vbNormalFocus)

AppActivate csvfile
SendKeys "%F"
SendKeys "A"
SendKeys excelfile 'rename the file as an .xls
SendKeys "%F"
SendKeys "x"

Any help is appreciated.

[ajetrumpet]

I don't know if sendkeys is appropriate here. I'm sure it works, but how about using an excel object instead? I wonder if that would work. for instance:

dim xl as object
set xl = getobject(, "excel.exe")
set wb = xl.workbooks("workbookname")

'save as code here
'close object and destroy

would that work too?? that's pseudocode, so don't take it verbatim. It's not completely correct.

don't know if opening a .csv that way would work with the "workbooks.open" method, but obviously it does through the interface, so I would assume it does otherwise, in code.

[ghutche]

That helps alot. Now I'm stuck on how to save the csv file as an xls file. Here is what I've tried. I get an error that the SaveAs method of the Workbook class failed. I'm not sure what that means.

Dim xl As Object

csvfile = Shell("excel.exe" & " " & TargetFile, vbNormalFocus)
Set xl = GetObject(TargetFile)
xl.SaveAs Filename:= _
excelfile, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
xl.excel.Quit
Set xl = Nothing

[ajetrumpet]

do the same thing that you're trying to do right now, In Excel. record your actions and copy the code there. that's how you work things like this when something doesn't work right.

[ghutche]

Based on the code that excel uses, I changed the language in Access to this

activeworkbook.SaveAs Filename:= _
excelfile, FileFormat:=xls, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

Now I get an error, "Object Required"

[ajetrumpet]

you get that error because you copied the code verbatim. You can't do that. this:

activeworkbook.SaveAs Filename:= _

changes to this:

xl.SaveAs Filename:= _

assuming you still have SET it, as your previous code did, right?

That's the way I would personally do it. I cannot check it for you, but built in macros don't lie, which is why this method works. It's never failed me, personally.

[ghutche]

You DA MAN! It worked great. Thanks for your help

[ajetrumpet]

solved...