?
Solved

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

Posted on 2011-10-06
18
Medium Priority
?
1,144 Views
Last Modified: 2012-05-12
Hi Experts,

I need your expertise on a project that I am currently working. I need to import a dynamic range from multiple spreadsheets into an temporary table in access for further processing. I have defined the ranges and the transferspreadsheet for import is working fine as well. However, I am facing the problem of an additional Excel instance getting created while the transferspreadsheet command is executed.

I have the excel file open, it defines the ranges based on key words and it executes the transfer spreadsheet command, post which I close the workbook and quit the excel instance... However, the file is still open.

Any thoughts or any better ways of getting this done? I need help in achieving this task without creating a new instance of excel or I should be able to close all instances of excel opened by my code at the end.

I am adding extracts of the code that I am using.

If xlApp Is Nothing Then
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = False
    xlOpen = True
    xlApp.ScreenUpdating = False
    xlApp.DisplayStatusBar = False
    xlApp.EnableEvents = False
End If

xBook = Split(![txtXmlPath], "\", , vbTextCompare)
xBook = xBook(UBound(xBook))

If IsFileOpen(.txtXmlPath) Then
    xlApp.Workbooks(xBook).Close False
    xlApp.Workbooks.Open .txtXmlPath, ReadOnly:=False, Notify:=False
Else
    xlApp.Workbooks.Open .txtXmlPath, ReadOnly:=False, Notify:=False
End If

If IsNumeric(.Check0) Then
    Set xSht = xlApp.Workbooks(xBook).Worksheets("920-" & Trim(.Check0))
Else
    Set xSht = xlApp.Workbooks(xBook).Worksheets(CStr(.Check0))
End If

Then I define the range

If IsTableExists("tblDeals") Then
    DoCmd.Close acTable, "tblDeals", acSaveNo
    DoCmd.DeleteObject acTable, "tblDeals"
End If

If IsTableExists("tbloBanners") Then
    DoCmd.Close acTable, "tbloBanners", acSaveNo
    DoCmd.DeleteObject acTable, "tbloBanners"
End If

If Not upcRge Is Nothing Then DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tblDeals", [Forms]![frmInput]![txtXmlPath], True, xSht.Name & "!" & Replace(upcRge.Address, "$", vbNullString, , , vbTextCompare)
If Not obRge Is Nothing Then DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tbloBanners", [Forms]![frmInput]![txtXmlPath], True, xSht.Name & "!" & Replace(obRge.Address, "$", vbNullString, , , vbTextCompare)

Thank you..
0
Comment
Question by:VenuChakkoth
  • 10
  • 5
16 Comments
 
LVL 11

Expert Comment

by:donaldmaloney
ID: 36928911
I have a module called:

modCloseExcelWorkbooks
which has the code   which I  call in my application to close all excel workbooks etc.

Once I am done importing a  worksheet I call the routine to make sure all open workbooks are closed.

IT sounds like you may have one open and it is causing issues.

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

Open in new window

0
 
LVL 1

Author Comment

by:VenuChakkoth
ID: 36928951
Thank you Donald.

We have a problem with closing all workbooks since we need to have other workbooks open while we import the data from excel to access.

Is there a way to kill just the instance of excel that is created while the docmd.transferspreadsheet (import) is done?
0
 
LVL 77

Expert Comment

by:peter57r
ID: 36929079
Are you certain that you are not creating any  'phantom' Excel  instance at any point by using an un-prefixed Excel object?
I see you have upcRge and obRge in your code which appear to be Excel objects but you haven't shown how they are declared and set..

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 11

Expert Comment

by:donaldmaloney
ID: 36931707
I close out all open excel objects   I shut down sheets and workbooks.

Once you have closed out of access do a CTR-ALT-DEL and loog at the running tasks.  If there is a EXCEL.EXE running then you still have an open object.

Do a search for every instance that you open one.
It can get tricky and I know you have just put in sample code - not all the code-  But peter is right about the upcRge and objRge.
0
 
LVL 1

Author Comment

by:VenuChakkoth
ID: 36931777
Hi Donald and Peter,

Sorry for the delayed response. I was traveling.

I will explain what I'm trying to do. There is one instance of Excel that is open. The upcRge and obRge are all part of that instance. However when I do the transferspreadsheet command a new instance is getting created that's causing the file locked for editing error notification and this is what I'm trying to fix. I hope I didn't confuse you...
0
 
LVL 11

Expert Comment

by:donaldmaloney
ID: 36936333
So when you run the transfer you are accessing  the xls field defined/named/stored in [Forms]![frmInput]![txtXmlPath].
Do you ever close the referenced Excell sheet/workbook?

Don
0
 
LVL 1

Author Comment

by:VenuChakkoth
ID: 36936423
Hi Don,
No, the referenced workbook is closed only after the transferspreadsheet is fired.
0
 
LVL 11

Expert Comment

by:donaldmaloney
ID: 36936988


What I do is also close the workbook after the transfer
e.g  code in my access app

For I = 1 To (intCount)
Set objSheet = objWkBook.Sheets(I)
  strWSname = objSheet.Name
   'Debug.Print i; intCount; strWSname  'use this for testing on the immediate window
   
 
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
        "" & tblName & strWSname & "", strFileName, True, "" & strWSname & "!"
 
     
Next I

objWkBook.Application.Quit
Set objWkBook = Nothing
Set objSheet = Nothing
0
 
LVL 1

Author Comment

by:VenuChakkoth
ID: 36937771
Hi Don,

I am following the same procedure to close the workbook and quit excel after running the docmd.transferspreadsheet.

However, what is happening is that the transferspreadsheet command is opening another instance of Excel and since its opening the same workbook again, I am getting the "File locked for editing" error notification. So., when the code closes the workbook and quits excel, I still have an open instance of excel and I cannot follow the code to kill all existing excel sessions because I have users working on other workbooks at the same time when the code is run.

Oh! one point that striked me now from your code is to try killing the excel instance using the objWkBook.application.quit method. I will try this and let you know.

Thank you...

0
 
LVL 1

Author Comment

by:VenuChakkoth
ID: 36937796
Hi,

I tried that as well. Even thought I have called application.quit after doing the transfer, I have the same workbook open in Read Only Mode and I get the notification that the file is available for editing. Choose Read Write to open it for editing...

Thank you
0
 
LVL 1

Author Comment

by:VenuChakkoth
ID: 36938171
Hi All,

Just to add on to my previous comments - My code works just fine if there are no other excel workbooks open at the time of running the code.

However, if there are other excel workbooks open, I will still have the workbook that is opened by the code, open as a read only version and I get the notification that the file is locked for editing,
0
 
LVL 1

Accepted Solution

by:
VenuChakkoth earned 0 total points
ID: 36938332
Hi All,

I have found a solution to my problem. I tried running this without creating a new instance of excel (it checks if there is an existing instance and if available, it uses the existing one without creating a new one). Its working fine now...

Thanks for all your time and efforts...
0
 
LVL 1

Author Comment

by:VenuChakkoth
ID: 36939101
I've requested that this question be deleted for the following reason:

I have figured out a solution to my problem. This question may be closed. Thank you
0
 
LVL 11

Expert Comment

by:donaldmaloney
ID: 36938907
I would leave it as open so other people will have the answer.  JUst ask if it can be made as comment and not use your points
0
 
LVL 1

Author Comment

by:VenuChakkoth
ID: 36939100
Thanks Don. I am not deleting the post.    Please consider this as my comment and donot use my points.
0
 
LVL 1

Author Closing Comment

by:VenuChakkoth
ID: 36972764
I figured out a solution to my problem. Refer to Comment ID: 36938332
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

MS Outlook undoubtedly is the most widely used email client.Its user-friendliness, cost effectiveness, and availability with Microsoft Office Suite make it the most popular email application.  Its compatibility with Microsoft applications like Exch…
In this article, I will demonstrate that how to do a PST migration from Exchange Server to Office 365. This method allows importing one single PST, or multiple PST's at once.
Progress
Introduction to Processes

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question