Error Message

[Guest]

Hi, When I run the following code, I see this error:
"Run-Time error '91': Object variable with block variable
not set"

Would someone be able to tell me what I'm doing wrong?
Thankc!

Dim z As Integer

Dim SheetCount As Integer
Dim SheetName(100) As String

Dim MessageText As String

Dim XLApp As Object
Set XLApp = GetObject(, "Excel.Application")

XLApp.Visible = True
XLFile = strInputFileName

SheetCount = XLApp.ActiveWorkbook.Sheets.Count

For z = 1 To SheetCount

SheetName(z) = XLApp.ActiveWorkbook.Sheets(z).Name
MessageText = MessageText & z & ".) " & SheetName(z) & " "
DoCmd.TransferSpreadsheet acImport,
acSpreadsheetTypeExcel8, XLApp.ActiveWorkbook.Sheets
(z).Name, XLFile, True, SheetName(z)

Next z

 

[Ken Snell]

You don't say on which line of code the error occurs, but I'l guess that
it's on the XLApp.Visible line. XLApp will not be set to anything if EXCEL
is not already running when you run this code. If you want the code to
"start" EXCEL, change

Set XLApp = GetObject(, "Excel.Application")

to the following lines:

On Error Resume Next
Set XLApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Err.Clear
Set XLApp = CreateObject("Excel.Application")
End If
On Error GoTo 0

 

[Guest]

Hi Ken -

Actually the error occurs on the
Docmd.TransferSpreadsheet... line.

I do not believe it is XLApp that is causing the error..

Thanks!!

 

[Ken Snell]

OK -

Why are you opening the workbook and then trying to import from it?
TransferSpreadsheet has to gain access to the file through its own
connections -- if you have it open already, I believe TransferSpreadsheet
will fail.

If you're opening it to get a worksheet name, then close the workbook after
you get it. Then do the TransferSpreadsheet using the stored name as the
"range" argument. Also, use the path and file name in TransferSpreadsheet,
not the full reference to the worksheet in the file.

 

[Guest]

Hi Ken-

Thanks for the quick response.
If I already know what the worksheet names are, can I
write a code that automatically imports the data from 2
worksheets into 2 separate tables (always named the same)
without having to open the file?

Also, I do not understand what you mean by 'using the
stored name as the "range" argument'.
Also, I do not know the range on my worksheet (the number
of rows may vary).
Lastly, I do not understand what you mean by 'use the
path and file name in TransferSpreadsheet and not the
fill reference to the worksheet in the file'

Thanks for your patients. I am new to Access &
programming..

 

[Guest]

Hi Ken - please ignore my message below i think i may
have figured it out. btw, it does import with the excel
file open..i think!

 

[Joyce Hardy]

To: <joycTo: <joycehardy@i-plusrdy@i-plus