Import only Works if Excel Worksheet is Open

[Moxioron]

Hello.

I have a module that is relatively straight forward:

Function PSCUInvoiceImport()
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "tbl_PSCU Invoice", "R:\DEPT-BR\Public\PSCU Invoice\PSCU Invoice.xls", True, "GL TOTALS!B750000"
End Function

The fields in the tbl_PSCU Invoice are as follows:
Element Column B
Element Name Column C
Service/Service Group Column D
Memo Column E
Item Type Column F
Item ID Column G
Type Column H
Qty Column I
Unit Price Column J
Base Amount Column K
Discount Column L
F12 Column M
Sales Tax Column N
F14 Column O
Net Amount Column P

I have the F12 and F14 in there because the header for Discount and Sales tax are merged cells (just the header).

If the spreadsheet is closed and I run the VBA, everything imports except Sales Tax and Net Amount. However, If I open the spreadsheet and run the VBA, those two fields populate.

I need to run this when the spreadsheet is not open. How can I do that and bring in all of the data?

This spreadsheet is from a third party and I don't have any control over what cells they merge and don't merge.

Thanks.

 

[Rx_]

Little different approach. The Excel can be opened but left invisible. That is my favorite way to manage it. Especially if we don't want someone to notice we are borrowing data.

Option Compare Database
Option Explicit
' Once the workbook reference is set, you can just as easy read any Excel range (of 1 to many cells) back into your variables.
Private Sub ExcelTest_Click()
    ' Rx  Basic code for Excel automation my mother never told me
     ' Basic Excel Automation from Access 2010
      'Variables to refer to Excel and Objects
      Dim MySheetPath As String
      Dim xl As Excel.Application
      Dim XlBook As Excel.Workbook
      Dim XlSheet As Excel.Worksheet
 
      ' location of actual Excel file
10    MySheetPath = "C:\Temp\importtest.xls"
 
      'Open Excel and the workbook
20    Set xl = CreateObject("Excel.Application")
30    Set XlBook = GetObject(MySheetPath)
 
      'Make sure excel is visible on the screen
      ' Set Xl.Visible to False if it doesn't need to be visible
40    xl.Visible = True
50    XlBook.Windows(1).Visible = True
 
      'Define the sheet in the Workbook as XlSheet
      ' Alternative:
      ' dim MyWorksheetName as String
      ' MyWorksheetName = "Rx"
      ' Set XlSheet = XlBook.Worksheets(MyWorksheetName)
60    Set XlSheet = XlBook.Worksheets(1)
 
      'Insert Row and the Value in the excel sheet starting at specified cell
70    XlSheet.Rows(2).EntireRow.Insert
80    XlSheet.Range("D2") = "Rx"
 
      ' Save and close
90    xl.ActiveWorkbook.SaveAs FileName:="C:Rx\testSave.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
100   xl.ActiveWorkbook.Close ' see note on next xomment
      'Rx_ ... Clean up this Object Variables mess and end with worksheet visible on the screen if it was visible
      ' and Xl.ActiveWorkbook.Close was not called (line 100)
110   Set xl = Nothing
120   Set XlBook = Nothing
130   Set XlSheet = Nothing
140  'Set Rx_ = Nothing
' Code written while listening to Mirror in the Bathroom by English Beat
End Sub

 

[Moxioron]

Sweet.I just plugged in this to the front of my module and whala.MySheetPath = "R:\DEPT-BR\Public\PSCU Invoice\PSCU Invoice.xls"CreateObject ("Excel.Application")Set XlBook = GetObject(MySheetPath)Thank you sir.