Error Message

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

- 16 Jul 2004 15:51 GMT

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

Reply to this Message

Ken Snell - 16 Jul 2004 16:31 GMT

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

Signature

Ken Snell
<MS ACCESS MVP>

> Hi, When I run the following code, I see this error:
> "Run-Time error '91': Object variable with block variable
[quoted text clipped - 27 lines]
>
> Next z

Reply to this Message

- 16 Jul 2004 16:39 GMT

Hi Ken -

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

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

Thanks!!

>-----Original Message-----
>You don't say on which line of code the error occurs, but I'l guess that
[quoted text clipped - 47 lines]
>
>.

Reply to this Message

Ken Snell - 16 Jul 2004 18:31 GMT

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.

Signature

Ken Snell
<MS ACCESS MVP>

> Hi Ken -
>
[quoted text clipped - 61 lines]
> >
> >.

Reply to this Message

- 16 Jul 2004 19:38 GMT

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..

>-----Original Message-----
>OK -
[quoted text clipped - 76 lines]
>
>.

Reply to this Message

- 16 Jul 2004 22:00 GMT

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!

>-----Original Message-----
>Hi Ken-
[quoted text clipped - 106 lines]
>>
>.

Reply to this Message

Joyce Hardy - 15 Aug 2004 08:51 GMT

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

Reply to this Message