Results 1 to 8 of 8

Using Shell in VBA

  1. #1
    ghutche is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2011
    Posts
    4

    Using Shell in VBA

    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.

  2. #2
    ajetrumpet is offline Banned
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,696
    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:
    Code:
    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.

  3. #3
    ghutche is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2011
    Posts
    4
    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

  4. #4
    ajetrumpet is offline Banned
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,696
    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.

  5. #5
    ghutche is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2011
    Posts
    4
    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"

  6. #6
    ajetrumpet is offline Banned
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,696
    you get that error because you copied the code verbatim. You can't do that. this:
    Code:
    activeworkbook.SaveAs Filename:= _
    changes to this:
    Code:
    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.

  7. #7
    ghutche is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2011
    Posts
    4
    You DA MAN! It worked great. Thanks for your help

  8. #8
    ajetrumpet is offline Banned
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,696
    solved...

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Call Shell() syntax
    By jgelpi16 in forum Programming
    Replies: 6
    Last Post: 03-07-2011, 12:56 PM
  2. Open (shell) a program and pass a file
    By Olszanski in forum Programming
    Replies: 2
    Last Post: 07-27-2010, 02:52 PM
  3. Replies: 4
    Last Post: 05-20-2010, 02:49 AM
  4. Shell with Batch Variables
    By robbyaube in forum Programming
    Replies: 0
    Last Post: 12-12-2005, 07:40 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums