Help with docmd.transferspreadsheet for importing a dynamic range from excel to a temporary table in Ms Access 2007

Option Compare Database

Function fTaskManagerKillProcesses(processname As String) ' if "EXCEL"  wil count # of Excel Processes
'based on code: http://msdn2.microsoft.com/en-us/library/aa394599.aspx
'Stop
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" _
    & strComputer & "\root\cimv2")
Set colProcessList = objWMIService.ExecQuery _
    ("Select * from Win32_Process")
For Each objProcess In colProcessList
    If InStr(1, UCase(objProcess.Name), UCase(processname)) > 0 Then
    'Stop
    objProcess.Terminate
   '     I = I + 1
    End If
Next
'CountProcesses = I

End Function
Public Function ClearAllRunningExcelWorkbooks()
  On Error Resume Next
  Dim oXL As Excel.Application
  Dim Loopcount As Single
  Loopcount = 0
  Set oXL = GetObject(, "Excel.Application")
  'If running this code from Excel, the first call will never have an error
  Do
    oXL.Quit
    Set oXL = Nothing
    Set oXL = GetObject(, "Excel.Application")
    Loopcount = Loopcount + 1
    If Loopcount > 100 Then
    
    MsgBox "Closing over 100 excel files?", , "Looping on close"
    Call fTaskManagerKillProcesses("EXCEL")
   'Stop
    If Not oXL Is Nothing Then oXL.Visible = True
    Exit Function
    End If
    Loop Until Err
  Set oXL = Nothing
  'Stop
End Function



Function CloseExcel()

Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRng As Excel.Range
Dim ExcelWasNotRunning As Boolean

Stop
'specify the workbook to work on

Err = 0
'If Excel is running, get a handle on it; otherwise start a new instance of Excel
On Error Resume Next
StartClearExcel:
Set oXL = GetObject(, "Excel.Application")

If Err Then
   ExcelWasNotRunning = True
   GoTo EndClearExcelRoutine ' added
'   Set oXL = New Excel.Application '  removed
End If

On Error GoTo Err_Handler

'If you want Excel to be visible, you could add the line: oXL.Visible = True here; but your code will run faster if you don't make it visible

'Open the workbook


'Process each of the spreadsheets in the workbook

Set oWB = oXL.ActiveWorkbook
For Each oSheet In oXL.ActiveWorkbook.Worksheets
   'put guts of your code here
   'get next sheet
   Set oSheet = Nothing
Next oSheet
oWB.Quit
Set oWB = Nothing
oXL.Quit
Set oXL = Nothing
'Next oWB
If ExcelWasNotRunning Then
  oXL.Quit
End If
ExcelWasNotRunning = False
'Next
GoTo StartClearExcel
EndClearExcelRoutine:
'Make sure you release object references.
Set oRng = Nothing
Set oSheet = Nothing
Set oWB = Nothing
Set oXL = Nothing

'quit
Exit Function

Err_Handler:
   MsgBox Err.Description, vbCritical, "Error: " & Err.Number
   If ExcelWasNotRunning Then
       oXL.Quit
   End If

End Function

Select allOpen in new window