Error message "Invalid outside procedure"

rmen220
rmen220 used Ask the Experts™
on
So I use the code below to import a range based on the last cell with data in column A.
 
When I do I believe this line of code "set xl = CreateObject("Excel.Application")" keeps creating the error message "invalid outside procedure"

I am pretty new at VBA. Do I just stick this code in a module in access and run it? Everytime I attempt to do so it asks me to create a macro. Could this be the problem?


Dim xl as object
Dim wb as object
Dim sht as object
Dim lngLastRow as long

set xl = CreateObject("Excel.Application")
set wb =xl.Workbooks.Open(strfilename)
set sht = wb.Worksheets(1)  'or wb.Worksheets("SheetName")

lngLastRow = sht.Range("A65536").End(xlup).row

wb.Close SaveChanges:=False
set xl = nothing

DoCmd.TransferSpreadsheet acImport, , _
    "Jan2013_RD_NonEnd", "data\AZ-Projects\9 Scorecard Reference Files\NPS\RawData-NonEnd.xlsx", True, Range("A1:U" & lngLastRow)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013
Commented:
Its not clear if you have done this, but place the code in a module, making sure the code is wrapped in a function or sub declaration like this:


Sub ExcelProcedure()

Dim xl as object
Dim wb as object
Dim sht as object
Dim lngLastRow as long

set xl = CreateObject("Excel.Application")
set wb =xl.Workbooks.Open(strfilename)
set sht = wb.Worksheets(1)  'or wb.Worksheets("SheetName")

lngLastRow = sht.Range("A65536").End(xlup).row

wb.Close SaveChanges:=False
set xl = nothing

DoCmd.TransferSpreadsheet acImport, , _
    "Jan2013_RD_NonEnd", "data\AZ-Projects\9 Scorecard Reference Files\NPS\RawData-NonEnd.xlsx", True, Range("A1:U" & lngLastRow)

End Sub

Open in new window


Save your module with a name that is different from the sub, like 'modExcelCode'
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
Also, I believe you'll have to modify your transfer spreadsheet command like this:


DoCmd.TransferSpreadsheet acImport, , _
    "Jan2013_RD_NonEnd", "data\AZ-Projects\9 Scorecard Reference Files\NPS\RawData-NonEnd.xlsx", True, "A1:U" & lngLastRow

Open in new window

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
I doubt that CreateObject( ) is the problem, as I have never really had a problem with that.  

If you put this code in a subroutine or function, as I would expect, did you pass it the variable for strFileName?  You could pass that via the declaration statement, or use the File Open/Save API to select the file.

I actually use a function to get an Excel object.  You would use it like:

set xl = GetExcel

and the function is below.  This function looks to see whether there is already an instance of Excel open, and if so, uses it.  If not, it opens a new one.  I have a lot of code that exports or reads data from Excel, so I found that creating this funtion allow me to avoid a lot of code in my various modules.
Public Function GetExcel() As Object    'Excel.Application

    On Error GoTo ProcError
    
    Set GetExcel = GetObject(, "Excel.Application")
    
    Exit Function

ProcError:
    If Err.Number = 429 Then
        Set GetExcel = CreateObject("Excel.Application")
    Else
        Set GetExcel = Nothing
    End If

End Function

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Sorry to be such a novice. Maybe I should explain what I am trying to do. I would like to create a procedure (not sure "procedure" is the right term) that I can call through a macro that will import the data range based on the code above. When the Sub ExcelProcedure() above I now get- Compile error: Sub or function not defined and the range portion of this part of the code is highlighted in blue Range("A1:U" & lngLastRow. Thank you for all your help. Thank goodness for EE
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
I didn't even look at that line when I provided my subroutine in your previous post, because you implied that it was working when you used that technique.  See mbizup's post above.

OK, calling this from a macro is new, why a macro when you could use code.  Although the Access wizards like to write code that uses macros, you can do so much more with VBA, and we are here to help!

I assume you want to call this when you press a button on your form, is that correct?  If so, are you displaying the name of the file you want to import from anywhere on the form?  Where are you getting that value from.  Can you take a screen shot (PrintScreen) of your form and post it here so we can see what it looks like?

Author

Commented:
So here is what I did I placed this code into a module:


Sub ExcelProcedure()

Dim xl As Object
Dim wb As Object
Dim sht As Object
Dim lngLastRow As Long

Set xl = CreateObject("Excel.Application")
Set wb = xl.Workbooks.Open(strfilename)
Set sht = wb.Worksheets(1)  'or wb.Worksheets("SheetName")

lngLastRow = sht.Range("A65536").End(xlup).row

wb.Close SaveChanges:=False
Set xl = Nothing

DoCmd.TransferSpreadsheet acImport, , _
    "Jan2013_RD_NonEnd", "C:\Documents and Settings\u0k0ao\Desktop\Book123.xls", True, "A1:U" & lngLastRow

end sub

Open in new window


You will notice i made the adjustment from mbizup's reply and removed range from
 ""A1:U" & lngLastRow" I now get the error

"runtime error 1004: "could not be found check the spelling of the filename and verify the location is correct"

The path in this portion of the code is def correct
DoCmd.TransferSpreadsheet acImport, , _
    "Jan2013_RD_NonEnd", "C:\Documents and Settings\u0k0ao\Desktop\Book123.xls", True, "A1:U" & lngLastRow

I suspect this may stem from fyed reply regarding this part of code and needing to make an adjustment there

Set wb = xl.Workbooks.Open(strfilename)

Can you please step me through?
This sorry to be so frustrating can adjust the points on this question to 500
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
Try this:

Sub ExcelProcedure()

Dim xl As Object
Dim wb As Object
Dim sht As Object
Dim lngLastRow As Long
Dim strFileName as string

Set xl = CreateObject("Excel.Application")

strFileName = "C:\Documents and Settings\u0k0ao\Desktop\Book123.xls"
Set wb = xl.Workbooks.Open(strfilename)

'If the sheet you are importing is the 1st worksheet in the workbook, you should
'be able to use either of these options.
Set sht = wb.Worksheets(1)  'or wb.Worksheets("Jan2013_RD_NonEnd")

lngLastRow = sht.Range("A65536").End(xlup).row

wb.Close SaveChanges:=False
Set xl = Nothing

DoCmd.TransferSpreadsheet acImport, , _
    "Jan2013_RD_NonEnd", strFileName, True, "A1:U" & lngLastRow

end sub

Open in new window

Author

Commented:
Maybe this will help I have data that is loaded into excel workbooks that house results for my agents I use excel to format that data then import into access where I link the tables to create one DB that combinds all the metrics from those excel WB's.
Some workbooks are updated daily, some weekly, some monthly. So I have to create a procedure be it macro or module that will allow me to designate when I want the data to import by running the macro or module.    

You are right "macro" was the wrong term I just want to run the procedure when I designate ex. click on the module

Author

Commented:
so I pasted the code above into my module and recieved the error message

Run Time error 1004

application defined or object defined error

and this portion of the code is highlighted

lngLastRow = sht.Range("A65536").end(xlup).row
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Never occured to me that you might not have Excel as a reference in your application.  Try:

lngLastRow = sht.Range("A65536").end(-4162).row

If you open the VBA window, then select Tools -> References, you can scroll down and check the box next to Microsoft Excel, this will give you the ability to use the Excel constants like xlup.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
As much as I appreciate all of these points (2 separate posts), I don't deserve them, since I didn't really get the first solution right to begin with.

As a minimum, you need to give some of these points to mbizup, as she recognized that the reference to the range in the TransferSpreadsheet call was wrong.

Just click the "Request Attention" button (bottom right of your original post) and ask the moderators to reopen the question for reallocation of points.

Author

Commented:
You are correct (sorry mbizup) I got overly excited having the solution. I am new to EE too. Dang I sure did learn alot with this one post
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Glad we could help.

Just some advise, although you may not know the right syntax, make sure you search the articles here before asking a question, unless you just have to have the answer right now.

There are very few Access questions that have not already been asked and answered here.

Also, Take a look at some of the Access Webinars.
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
rmen220,

Welcome aboard!

No worries - I was in a meeting.  Glad you're sorted.

One of the best things you can do to get the most out of your membership here is to go through the pages on asking questions in EE's help.  It contains a lot of valuable info with screenshots and screen casts to boot.



Dale,

Thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial