Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    togo is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2012
    Posts
    83

    Trying to run a macro in Excel from inside Access

    Hi. I've been opening comma delimited files in Access, used an Excel function to re-save those files in .xls which Access readily understands, then opening said files and running a query on them to organize them. Now the file needs to go to Excel where it is entered into a worksheet with a certain heading.

    Problem is when opening the .xls converted file in Excel, no macros show up. If I open a blank worksheet in Excel my macro shows up. I lowered the security settings to the most basic level in Excel. How do I make the macro show up in the file opened in Excel through the Access VBA, so I can run it?

    Thanks

  2. #2
    help_me_with_access is offline help_me_with_excel
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    262
    what macros? did you create them in excel?

    what is the source of these so-called "macros"? I'm confused.

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Also confused. None of what you describe is comprehensible.

    You open which files (CSV or Excel) and 'run' query on. How does running a query 'reorganize' files?

    How can Excel file created from CSV have a macro? How can a new 'blank' worksheet have a macro?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    togo is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2012
    Posts
    83
    yes the macro is an excel macro, one I made, really short test one that replaces the contents of a few cells.

    I used Excel to open .CSV files and save them as .XLS files, so they can be opened in Access. I use VBA in Access to do this by opening up a temporary Excel object.

    The file will then be saved in .XLS format from Access VBA code (Excel temporary object) and then opened in Excel again through VBA in access, using a temporary Excel object. I have inserted the personal.xlsx file (which supposedly is macro-enabled and includes my test macro) into XLSTART directory so that any new blank worksheet in Excel has this test macro in it, which I will later modify. However when double clicking on a .XLS file, the macro does not show up.

  5. #5
    togo is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2012
    Posts
    83
    I'm toying with the idea of using Excel to do the entire thing, using a pivot table. But I was hoping to make this process as easy as possible for the end user, and I understand that Excel doesn't really take command line arguments. As it stands all that is required so far is to click and drag the source file onto a batch file which then starts this chain of events.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    AFAIK, an Excel2007/2010 file must be saved as xlsm, not xlsx, to have macros, otherwise the code is eliminated.

    However, if you see macro in a new workbook, then the XLSTART file is actually an xls, not xlsx?

    If you open an xls that was not created from the XLSTART template, then I would not expect the macro to show.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    togo is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2012
    Posts
    83
    alright I've renamed the extension in VBA code to .xlsm

    9 out of 10 tries though when running this script, it tells me "ActiveX component can't create object"

    or if a session of Excel is open to boot,

    "The remote server machine does not exist or is unavailable"

    if I play with it enough it usually decides to work though for some odd reason. I manually saved it as a macro enabled work book which did work, and does this have something to do with it ?

    Code:
    ActiveWorkbook.SaveAs FileName:=Left(strCSVPath, Len(strCSVPath) - 3) & "xlsm" _
            , FileFormat:=52
    file format = 52, is this correct?

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Apparently - http://www.rondebruin.nl/saveas.htm

    Regardless of how the XLSTART file is saved and named, if a file is not created from that template then I doubt the macro will be in that created file.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    togo is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2012
    Posts
    83
    my macro is unable to save the file but it did open it, I then saved it as .xlsm, and it was macro enabled upon re-opening it.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Do you still have an issue?

    Still not understanding the process you want to happen.

    Want to describe the process as numbered steps?

    Want to post the entire code procedure?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    togo is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2012
    Posts
    83
    Absolutely:

    Code:
    Option Compare Database
    
    Public Sub ConvertCSVtoXL(strCSVPath As String)
    
    
    Dim appExcel As Excel.Application
    
    
    'Switch to Microsoft Excel so it won't go away when you finish.
        On Error Resume Next
        AppActivate "Microsoft Excel"
    
    
        
        'If Excel isn't running, start and activate it
        If Err Then
        Shell "c:\Program Files\Microsoft Office\Office\" _
            & "Excel /Automation", vbHide
        AppActivate "Microsoft Excel"
        End If
        On Error GoTo 0
        
        'Get an Application object so you can automate Excel.
        Set appExcel = GetObject(, "Excel.Application")
    
    
        With appExcel
        
        .Workbooks.Open FileName:=strCSVPath
        ActiveWorkbook.SaveAs FileName:=Left(strCSVPath, Len(strCSVPath) - 3) & "xlsm" _
            , FileFormat:=52
        End With
        
        appExcel.Quit
    Set appExcel = Nothing
        
        MsgBox "File '" & strCSVPath & "' has been converted to excel under the same " & _
        "filename with an XLSM extension"
    
    
    End Sub
    '------------------------------------------------------------
    ' Macro11
    '
    '------------------------------------------------------------
    Function Macro11()
    On Error GoTo Macro11_Err
        'Call AddNew
        ConvertCSVtoXL "C:\PMI\temp1.csv"
    
    
    
    
    Macro11_Exit:
        Exit Function
    
    
    Macro11_Err:
        MsgBox Error$
        Resume Macro11_Exit
    
    
    End Function
    1) Macro1 uses the above code to convert the CSV file to a XLS file (by calling Macro11())
    2) Macro1 links the resulting file (temp1.xls) to a table named "temp" in Access
    3) Macro1 runs a query
    4) Macro1 will then save the file in an Excel format, open the file in Excel and begin an Excel macro (this step not done yet)

    hopefully this makes sense

    as for those last two errors (""The remote server machine does not exist or is unavailable" and ""ActiveX component can't create object") these are separate issues AFAIK that i didn't have at work but have at home, so I'm somewhat willing to overlook them. But it negates my ability to test at home.

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Nope, still not clear. Where is this code residing?

    Macro11 calls ConvertCSVtoXL. I don't see anything named Macro1.

    So somehow code saves CSV file as xlsm, fine, and you say this new file has code? Because the Excel app was opened first and the XLSTART file brings in the code (what code)?

    The posted code works? What exactly is the issue you need to fix?

    What is it you want to accomplish by converting CSV to Excel? Why Excel?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    togo is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2012
    Posts
    83
    Macro1 is called by the batch file that boots up Access. Macro loads with Access, I didn't bother converting it to VB code.

    the CSV file is saved as xlsm (which isn't working). I don't think the CSV itself had any code in it. But the goal is to run a macro in Excel after opening this file. The code for this last step doesn't exist yet.

    the issue could be fixed but because of these other two errors I've been talking about, I've been unable to test it. A header file has been designed in excel and they want this data to go into the header file.

  14. #14
    help_me_with_access is offline help_me_with_excel
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    262
    June,

    I don't think this person knows what they're doing...FYI.

  15. #15
    togo is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2012
    Posts
    83
    Quote Originally Posted by help_me_with_access View Post
    June,

    I don't think this person knows what they're doing...FYI.
    I have a pretty broad history with visual basic but this is mostly new to me (microsoft office products)

    nothing to be afraid of, most of my plans have worked with it so far. It's easy to copy other people's code, and even easier to modify it. Saves hours.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Execute Excel Macro from Access failing
    By dcgrove in forum Programming
    Replies: 1
    Last Post: 12-22-2011, 02:14 PM
  2. Excel Import Macro Help in Access
    By vennies83 in forum Import/Export Data
    Replies: 5
    Last Post: 11-02-2011, 09:24 AM
  3. Converting Excel Macro into Access Module
    By diddyville in forum Modules
    Replies: 1
    Last Post: 03-28-2011, 07:02 PM
  4. Replies: 1
    Last Post: 10-15-2010, 06:09 AM
  5. Running excel macro from access
    By timpepu in forum Programming
    Replies: 1
    Last Post: 02-26-2010, 11:32 PM

Posting Permissions

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