Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-05-2019, 05:33 AM   #1
abenitez77
Newly Registered User
 
Join Date: Apr 2010
Posts: 127
Thanks: 5
Thanked 0 Times in 0 Posts
abenitez77 is on a distinguished road
Question app hangs

My app is hanging when I try to close an excel workbook. I am linking an excel file and when I am done processing and want to close the workbook, the app hangs. What am I not doing right?

It hangs on this line:
wkb.Close


Code:
Dim objXL As New Excel.Application
   Dim wkb As Excel.Workbook
   Dim wks As Excel.Sheets
   Set db = CurrentDb
   Dim tdf As DAO.TableDef
   Dim fld As DAO.field
   Dim i As Integer
   Dim x As Integer
   Dim ShtCount As Integer

   PathFilename = Right(strFileName, Len(strFileName) - InStrRev(strFileName, "\"))
   shortFilename = Left(PathFilename, (InStr(PathFilename, ".") - 1))

   Set wkb = objXL.Workbooks.Open(strFileName)
   ShtCount = wkb.Sheets.count
   
   i = 1
   x = 1

   For Each wks In wkb.Worksheets
        'Progress Bar :::::::::::::::::::::::::::::::::::::
'        RetVal = SysCmd(acSysCmdInitMeter, "Linking FILE: " & PathFilename & " - SHEET: " & Trim(str(x)) & " of " & Trim(str(ShtCount)), ShtCount)
        'Update the progress meter.
'        RetVal = SysCmd(acSysCmdUpdateMeter, i)
        
        ' Get the Sheet Order
        wksindex = wks.Index
        
        ' Link each sheet of the entire Spreadsheet file -----------------------
        DoCmd.TransferSpreadsheet acLink, , _
              "TmpLinkXLS", strFileName, False, wks.Name & "$"
        
        ' Get the list of field names
        Set tdf = db.TableDefs("TmpLinkXLS")
        ' ---- Pause .5 second ------
        Const cTIME = 500 'in MilliSeconds
        
        Call sSleep(cTIME)
        
        tdf.RefreshLink
        ' ---- Pause .5 second ------
        Call sSleep(cTIME)
        
        myfields = ""
        MyLen = ""
        For Each fld In tdf.Fields
            myfields = myfields & "[" & fld.Name & "],"
            MyLen = MyLen & "Len(Trim(x1." & fld.Name & ")) > 0 OR "
        Next
        
        MyLen = Mid(MyLen, 1, Len(MyLen) - 3)
        
        i = i + 1
 '       RetVal = SysCmd(acSysCmdInitMeter, "Inserting FILE: " & PathFilename & " - SHEET: " & Trim(str(x)) & " of " & Trim(str(ShtCount)), ShtCount)
        
        'Update the progress meter.
 '       RetVal = SysCmd(acSysCmdUpdateMeter, i)
        
        ' Insert the data from the sheet into the local XLSData Table --------------------------
        strsql = "Insert Into XLSData(" & myfields & "Fullimagepath,TabName,xlsFileName,SheetOrder,Hdrid" & ") " & _
                    " Select " & myfields & Chr(34) & strFileName & Chr(34) & " as Fullimagepath, " & Chr(34) & wks.Name & Chr(34) & " as TabName, " & Chr(34) & shortFilename & Chr(34) & " as xlsfilename, " & Chr(34) & wksindex & Chr(34) & " as SheetOrder,  " & Chr(34) & Hdrid & Chr(34) & " as Hdrid" & _
                    " From TmpLinkXLS As x1 " & _
                    " Where " & MyLen
        CurrentDb.Execute strsql, dbFailOnError
        
        ' ---- Pause .5 second ------
        'Const cTIME = 1000 'in MilliSeconds
        Call sSleep(cTIME)
        
        i = i + 1
'        RetVal = SysCmd(acSysCmdInitMeter, "Dropping TmpLinkXLS", ShtCount)
        'Update the progress meter.
'        RetVal = SysCmd(acSysCmdUpdateMeter, i)
        
        If TableExists("TmpLinkXLS") Then
            DoCmd.DeleteObject acTable, "TmpLinkXLS"
            'CurrentDb.Execute "Drop Table TmpLinkXLS", dbFailOnError
        End If
        
        Set tdf = Nothing
        
        RetVal = SysCmd(acSysCmdInitMeter, " ", 0)
        RetVal = SysCmd(acSysCmdRemoveMeter)
        
        i = 1
        x = x + 1
   Next

   'Tidy up
   Set tdf = Nothing
   Set db = Nothing
   wkb.Close
   Set wkb = Nothing
   objXL.Quit
   Set objXL = Nothing


Last edited by abenitez77; 02-05-2019 at 05:39 AM. Reason: additional info
abenitez77 is offline   Reply With Quote
Old 02-05-2019, 06:01 AM   #2
abenitez77
Newly Registered User
 
Join Date: Apr 2010
Posts: 127
Thanks: 5
Thanked 0 Times in 0 Posts
abenitez77 is on a distinguished road
Re: app hangs

I found my answer...I had to add false at the end of wks.close:

wks.close False
abenitez77 is offline   Reply With Quote
Reply

Tags
linked excel file

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
ExecuteScalar hangs Milton VB.NET 3 10-20-2008 07:59 AM
Hangs & Exit azzy General 3 08-22-2005 04:50 AM
access hangs cippy Reports 1 06-08-2004 08:26 AM
Access Hangs TiggerNYC Modules & VBA 3 08-11-2003 04:43 AM
Report hangs AlanS Reports 2 05-12-2003 09:52 AM




All times are GMT -8. The time now is 09:59 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World