I have got to be missing something simple here. The following code I have generates an Excel sheet for me in an annoying format required to upload through some shipping software we use:
Code:
Private Sub btn_Shipment_Upload_Click()
'Generate Excel Workbook
Dim Excel_App As Excel.Application
Set Excel_App = CreateObject("Excel.Application")
Excel_App.Visible = True
Excel_App.Workbooks.Add
'Input required information
With Excel_App
.Worksheets(1).Cells(1, 1) = "PlanName"
.Worksheets(1).Cells(1, 2) = Me.Generate_Name
.Worksheets(1).Cells(2, 1) = "ShipToCountry"
.Worksheets(1).Cells(2, 1) = Me.Country
'ect... until row 13
'Run Make Query to Generate Item Recordset
DoCmd.SetWarnings (WarningsOff)
DoCmd.OpenQuery "qry_Pending_Item_Upload"
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tbl_Item_Upload")
'Start Pasting at Row 13
lngCount = 13
With rst
Do Until .EOF
Worksheets(1).Cells(lngCount, 1) = rst![Item]
Worksheets(1).Cells(lngCount, 2) = rst![Quantity]
.MoveNext
lngCount = lngCount + 1
Loop
rst.Close
Set rst = Nothing
End With
End With
End Sub
So all of this works totally fine... at least totally fine every other time you hit this button. The first time you select the button, the Excel worksheet is brought up and everything is fanflipintastic! Close the worksheet, select your next source and hit the button again, the worksheet generates with all of the manual info up until row 13 then fails with "Method 'Worksheets' of object '_Global' failed". Close the worksheet, and hit the button again, everything is totally fine. So every other time the button is selected it fails.
It's probably the lame code I put together, and something (hopefully) simple I'm missing. Any help would be appreciated!