subscript out of range if i run twice

[sambrierley]

Hi all,

the below code exports a table (via a function) to a spreadsheet and saves it in a defined location. the code then opens the file does some work with it then moves it(left the move bit out as it works fine)

so when i run the code it works absolutle fine, table is exported, work is done and file is moved. however if i run the code again it fails, i get the error message out of range. i was originally getting this error when using the .usedrange.copy so i commented this out and now i get it on the next line that tries to work with the file so obviously something is wrong in my logic. can anyone see what it is?

thanks

Private Sub Export2JDE_Click()
Application.Run ("JDE_Export") 'Exports to an xlsx file in the location described in the function.
Dim xlApp As excel.Application
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
xlApp.Workbooks.Open "T:\Inter Urban\Consultancy Services\Timesheets\Templates\Access\JDE.xlsx", True, False ' opens the recently exported file.
Set xlApp = Nothing
'Workbooks("JDE.xlsx").Sheets("TempHours").UsedRange.Copy 'copies all data,inc headings!!!!!
Dim Batch As Integer 'Batch number taken from JDE
Batch = InputBox("Your Export has been successfull. copy the Data into JDE and put the batch number in the box provided")
'DoCmd.RunSQL "INSERT INTO Archive  SELECT * FROM TempHours"

Workbooks("JDE.xlsx").Sheets("TempHours").Cells(1, 18).Value = "Batch" 'inserts Batch as the heading
Workbooks("JDE.xlsx").Sheets("TempHours").Range(Cells(2, 18), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 18)).Value = Batch 'inserts batch number into the xlsx file
ActiveWorkbook.Save
excel.Application.Quit

 Dim MyFile As String
 Dim MyPath As String
 MyPath = "T:\Inter Urban\Consultancy Services\Timesheets\Templates\Access\JDE.xlsx"
 
 'imports the JDE export into the archive with the batch number.
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Archive", _
MyPath, True, "TempHours!A:R"

 

[JHB]

..i get the error message out of range. i was originally getting this error when using the .usedrange.copy so i commented this out and now i get it on the next line that tries to work with the file so obviously something is wrong in my logic. can anyone see what it is?

thanks

..
xlApp.Workbooks.Open "T:\Inter Urban\Consultancy Services\Timesheets\Templates\Access\JDE.xlsx", True, False ' opens the recently exported file.
[B][COLOR=Red]Set xlApp = Nothing[/COLOR][/B]
'Workbooks("JDE.xlsx").Sheets("TempHours").UsedRange.Copy 'copies all data,inc headings!!!!!

1. You are setting the Excel.Application to nothing before you are finish working with it!
2. Workbooks is unknown in MS-Access, so you need to use the references to Excel. Like you do here xlApp.Workbooks.

 

[sambrierley]

Good Call on that one but unfortunalty not, ive moved the setxlapp= nothing to the end of the sub and makes no difference what so ever, same problem the second time i run the script

 

[JHB]

Did you also put in the references to Excel as I mention?

 

[sambrierley]

its the same workbook so do i need multiple references?

 

[JHB]

its the same workbook so do i need multiple references?

How should MS-Access know that, (read post #2, section 2)?

 

[sambrierley]

Thanks for that, I still dont understand why it would work the first time but o well.
I have made your changes and now im getting a whole new error

xlApp.Workbooks("JDE1.xlsx").Sheets("TempHours").Range(Cells(2, 18), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 18)).Value = Batch

the above line is reporting method cells of object_global failed.
again i dont get this error every time.

 

[vbaInet]

There's a lot of implicit object references there, you need to fix all that.
The following link shows different ways of exporting to Excel:

http://www.btabdevelopment.com/ts/exportformrecordset

I don't see the point of re-opening the Excel sheet to change the data if you could have already done that in the query that was exported.

 

[JHB]

Thanks for that, I still dont understand why it would work the first time but o well.

Yes it is a pity that MS-Access allows it to run the first time though lacking references to Excel in the code, because it misleads people into thinking the code is okay, which it is not.

Like vbaInet mention, there's a lot of implicit object references there, you need to fix all that.

  [B][COLOR=Red]xlApp.[/COLOR][/B]Workbooks("JDE.xlsx").Sheets("TempHours").cells(1, 18).Value = "Batch" 'inserts Batch as the heading
  [B][COLOR=Red]xlApp.[/COLOR][/B]Workbooks("JDE.xlsx").Sheets("TempHours").Range([B][COLOR=Red]xlApp.[/COLOR][/B]cells(2, 18), [B][COLOR=Red]xlApp.[/COLOR][/B]cells([B][COLOR=Red]xlApp.[/COLOR][/B]cells([B][COLOR=Red]xlApp.[/COLOR][/B]rows.Count, 1).End(xlUp).Row, 18)).Value = Batch 'inserts batch number into the xlsx file
  [B][COLOR=Red]xlApp.[/COLOR][/B]ActiveWorkbook.Save