DoCmd.TransferSpreadSheet

[Leo]

Hi again-

Need help...I've been spending a lot of time trying to
figure out the problem below:

When using the below code, I realized that the
application is importing the 1st worksheet in my excel
workbook over again (as opposed to importing the 2nd one).

Appreciate if you could tell me if my code is right!
Thanks!!

Private Sub cmd_Import_Click()

Dim XLApp As Object
Dim XLFile As String
Dim XLSheet As String
Dim XLwb As Object

Dim z As Integer
Dim SheetCount As Integer

Dim strFilter As String
Dim strInputFileName As String

strFilter = ahtAddFilterItem(strFilter, "Excel
(*.XLS)", "*.XLS")
strInputFileName = ahtCommonFileOpenSave
(Filter:=strFilter, OpenFile:=True, DialogTitle:="Please
select location of file...", Flags:=ahtOFN_HIDEREADONLY)

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

XLFile = strInputFileName

Set XLwb = XLApp.workbooks.Open(XLFile)

SheetCount = XLApp.activeworkbook.sheets.Count

For z = 1 To SheetCount - 1

XLSheet = XLApp.activeworkbook.sheets(z).Name
DoCmd.TransferSpreadsheet acImport,
acSpreadsheetTypeExcel8, XLApp.activeworkbook.sheets
(z).Name, XLFile, True

Next z

MsgBox "Imported Successfully"

XLApp.Quit

Set XLApp = Nothing
Set XLwb = Nothing


End Sub

 

[david epsom dot com dot au]

For z = 1 To SheetCount - 1

That can't be right: either it is

For z = 1 To SheetCount

or

For z = 0 To SheetCount - 1

(david)

 

[Leo]

Hi

I tried the below 2 suggestions and I get the 'block
variable not defined' error.
However, using For z = 1 To SheetCount - 1, it imports the
first sheet twice.

Please advise
Thanks
!

 

[Leo]

Please ignore my below email. I resolved it!

-----Original Message-----
Hi

I tried the below 2 suggestions and I get the 'block
variable not defined' error.
However, using For z = 1 To SheetCount - 1, it imports the
first sheet twice.

Please advise
Thanks
!

.