Receiving a VBA error msg

[blakemckenna]

I keep getting the following error message:

1004: The macro 'ThisWorkbook.openForProcessing(false)' cannont be found

What is being done here is an Access DB is generating a series of spreadsheets. I'm not sure how it works but an already existing blank XLS file called "FormatFieldReports.xls" is opened thru Access using VBA code. The 'openForProcessing' is a Public procedure within the latter spreadsheet file. The 'ThisWorkbook' is an object within the file as well.

I have posted the code that opens the "FormatFieldReport.xls" file.

Function formatXLFiles(userChoice As Boolean)
    Dim xlApp, myXLWrkBook As Object
    Dim databasePath
    Dim XLMacroPath
    
    databasePath = ExtractPath(CurrentDb.Name)
    XLMacroPath = databasePath & "\FormatFieldReports.xls"
    
    'Open connection to Excel Workbook and make it visible
    Set xlApp = CreateObject("Excel.Application")
    
    Set myXLWrkBook = GetObject(XLMacroPath)
    
    
    myXLWrkBook.Application.Visible = True
    myXLWrkBook.Application.Windows(1).Visible = True
    
    'Run procedure in ThisWorkBook folder
    If userChoice Then
        myXLWrkBook.Application.Run "ThisWorkbook.openForProcessing(true)"
    Else
        myXLWrkBook.Application.Run "ThisWorkbook.openForProcessing(false)"
    End If
    
    'Close Automation object
    'Either invoke the save method or set the Saved property to true to avoid a prompt about saving changes
    'myXLWrkBook.Application.ActiveWorkbook.Save
    myXLWrkBook.Application.ActiveWorkbook.Saved = True
    myXLWrkBook.Application.ActiveWorkbook.Close
    
    Set myXLWrkBook = Nothing
    
    xlApp.Quit
    Set xlApp = Nothing
End Function

Anyone have any ideas?

Thanks,

Blake

[blakemckenna]

Need some help folks....I'm drawing blanks on this one...

Thanks!

[Uche]

so now tell us wat u needed it to be ?

[salvelinus]

I'm pretty shaky on Excel, but if I understand you correctly, you're trying to run a custom procedure that's in Excel from Access. I don't know that you can do that. It looks like Access isn't seeing your openForProcessing procedure because it's in Excel (and maybe couldn't run it if it did).
Access can run the regular Excel methods & functions because they're built into the object model (as long as you've added a reference to it).
I may be wrong, but that's a best guess. If correct, you may have to put your custom code in a .dll and add a reference to that, or in some way switch from Access to Excel.

[blakemckenna]

Guys,

This app was actually running until I started messing with it. So I know it does work. The "ThisWorkbook" is an Excel Object. I'm just not sure why I'm getting this error message now. I probably did something without knowing it.

[salvelinus]

Ok, I googled around and found two possible causes.
1. Make sure the macroname is spelled correctly. This seems to be the most common error.
2. Remove the parantheses around the true/false parameters, per here.
3.Other possibilities: here, here, or this:
When you copy a Function from one Excel spreadsheet and paste it into
another Excel spreadsheet, make sure you are pasting the code into a Module,
not the VB code page for a worksheet.

[blakemckenna]

Hey salve,

I tried some of the suggestions you made and they didn't seem to work...but then again, I may have made a mistake. I think your pretty close so I've attached the current block of code that actually does the call to this spreadsheet.

Function formatXLFiles(userChoice As Boolean)
    Dim xlApp, myXLWrkBook As Object
    Dim databasePath
    Dim XLMacroPath

    databasePath = ExtractPath(CurrentDb.Name)

    XLMacroPath = databasePath & "\FormatFieldReports.xls"
    
    'Open connection to Excel Workbook and make it visible
    Set xlApp = CreateObject("Excel.Application")
    Set myXLWrkBook = GetObject(XLMacroPath)
    
    myXLWrkBook.Application.Visible = True
    myXLWrkBook.Application.Windows(1).Visible = True



    'HERE IS THE CALL TO THE SPREADSHEET. The name of the spreadsheet is
    'FormatFieldReport.xls'. The openForProcessing is a Sub Procedure located 
    'in the spreadsheet. "ThisWorkbook" is a class within this file as well. 

    'Run procedure in ThisWorkBook folder
    If userChoice Then
        myXLWrkBook.Application.Run "ThisWorkbook.openForProcessing(true)"
    Else
        myXLWrkBook.Application.Run "ThisWorkbook.openForProcessing(false)"
    End If


    
    'Close Automation object
    'Either invoke the save method or set the Saved property to true to avoid a prompt about saving changes
    'myXLWrkBook.Application.ActiveWorkbook.Save
    myXLWrkBook.Application.ActiveWorkbook.Saved = True
    myXLWrkBook.Application.ActiveWorkbook.Close
    
    Set myXLWrkBook = Nothing
    xlApp.Quit
    Set xlApp = Nothing
End Function

Hope this helps!!!!

Thanks,

Blake

[salvelinus]

Like I said, I don't know as much about Excel, more used to Access.
Is openForProcessing a procedure in the worksheet code ("ThisWorksheet") or in a separate module? Is the path to the .xls file correct? etc.
Try putting a breakpoint where the code starts at databasepath = ... and stepping through the code line by line, examing the values of the variables.

[blakemckenna]

Salve,

The openForProcessing is a procedure located in the "ThisWorkbook" code (within the FormatFieldReport.xls file). I've tried putting breakpoints in and it never takes me to the FormatFieldReport.xls file.

[salvelinus]

What line throws the error? Try putting the procedure in a module and just calling it from the Excel file, without the "ThisWorkbook".

[blakemckenna]

I'm at my wits end.....I don't know what the problem is.....nothing works!!!!!