Object variable or with block variable not set

[MobiusDick]

This forecasting tool is turning out to be quite a lot of hassle.

Have written a sub within a module which makes the user answer a specific set of questions before the sub transfers an excel sheet to a table- I have used this sub before with no problems (albeit in form modules rather than standard modules but nonetheless the same code)- however, when I run it now I get the error:

"Object Variable or With Block Variable not set"

at the line:

xlApp = "C:\Documents and Settings\All Users\Start Menu\Programs\Microsoft Excel"

I have tried declaring xlApp in the sub, at the start of the module and as a public variable to no avail.

Any suggestions?

 

[boblarson]

You don't assign that to it that way.
If you set a reference to Excel (Tools > References) in the VBA window you can use early binding:

Dim xlApp As Excel.Application

Set xlApp = New Excel.Application


And if you want to use late binding:

Dim xlApp As Object

Set xlApp = CreateObject("Excel.Application")

 

[MobiusDick]

Hi Bob, Thanks for the reply; however, I was already using the late binding method you have described please see code below:

myTableName = "tblHHForecast"
myFullExcelSourcePathName = "M:\Forecast Calculation Data\HH Forecast\HH Forecast.xls" '''Full path of your source Excel data file
xlApp = "C:\Documents and Settings\All Users\Start Menu\Programs\Microsoft Excel"   '''Where Microsoft Excel is on your computer
xlBook = "M:\Forecast Calculation Data\HH Forecast\HH Forecast.xls" '''Again, where the Excel source data file is
                            
Set xlApp = CreateObject("Excel.Application") '''Defines Excel as the app to use
xlApp.Visible = False
Set xlBook = xlApp.Workbooks.Open(myFullExcelSourcePathName) '''Opens the source data file

i = 0
                            
For Each xlSheet In xlBook.Worksheets '''Specifies which sheet you would
i = i + 1
ReDim strSheetNames(i)
strSheetNames(i) = xlSheet.Name
Next
                            
xlBook.Close False  '''Closes the source data file without saving
xlApp.Quit          '''Quits Excel
                            
Set xlBook = Nothing '''Clears all cache
Set xlApp = Nothing
                            
For i = 1 To UBound(strSheetNames)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, myTableName, myFullExcelSourcePathName, _
myRangeHasHeaderRow = True, strSheetNames(i) '''Imports data from spreadsheet

Next

 

[boblarson]

Maybe I"m missing something but why do you have these two lines:

xlApp = "C:\Documents and Settings\All Users\Start Menu\Programs\Microsoft Excel" '''Where Microsoft Excel is on your computer
xlBook = "M:\Forecast Calculation Data\HH Forecast\HH Forecast.xls" '''Again, where the Excel source data file is


they would definitely cause a problem since they are assigning a string to the wrong datatypes. Get rid of those two lines and it SHOULD get better.

 

[MobiusDick]

Hi Bob,

Thanks very much for that it seems to have solved the problem- I didn't help myself by not setting references to recognise Excel too.

Mark

 

[boblarson]

Hi Bob,

Thanks very much for that it seems to have solved the problem- I didn't help myself by not setting references to recognise Excel too.

Mark

Well, when using
Set xlApp = CreateObject("Excel.Application")

You shouldn't need to set a reference.

If you set the reference you can get the use of intellisense by using
Dim xlApp As Excel.Application

Set xlApp = New Excel.Application

 

[MobiusDick]

Not sure what's going on then as it didn't work prior to setting the references and now it seems to. Prior to setting the references I was getting a User defined variable error.

It all seems weird.

Thank you for your help though- wouldn't have been able to solve it without.