Results 1 to 3 of 3
  1. #1
    Ricardo Caicedo is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Aug 2014
    Posts
    11

    Opening multiple excel files from VBA Access macro - Error 1004 or duplicate data

    Hello Gurus

    I try to open two excel files from VBA Access (inside a macro) and it looks like Excel is still running in the background. I research about Using Marshal.ReleaseComObject() or killing the Excel.exe process but nothing works.

    First time the excel file is open and posted to a Table (TableBillData) and then when try to open the second one (Table2) a 1004 error shows up. If I reset the program then both tables are shown as the same data, so that means the variables are not released, and even you see the program opens the second file, the tables extract are the same. See down here the code. ANY HELP WILL BE GREATLY APPRECIATED


    Code:
    Sub UpdateMOAllCustomers()
     
    '------- UPDATE CUSTOMERS TABLE and at the same time update missing info Premise/BP/Ffee BASED ON UNBILL/BILL REPORT FROM SAP
    '
    ------- Program uses premise number so makes all the stadistics based on premises not CA
     
    Dim Ano 
    As StringMes As StringMesRep As StringstrSQl As StringstrSQ3 As StringAnoPrevS As StringMesPrevS As String
    Dim DataRange 
    As StringAs DoubleFranchise As DoublePastFile As BooleanActiva As String
    Dim MesPost 
    As LongAnoPos As LongTypeCA As StringBusiness As DoubleMoveOutc As DateFechaMO As Date
    Dim Index 
    As IntegerDuplicate As IntegerDateValidate As DateDateC As DateDatemo As DateMesAnt As Date
    Dim xlApp 
    As Excel.ApplicationxlApp2 As Excel.ApplicationCopyContract As DoubleMoveInT As Datesheetname As Stringsheetname2 As String
    Dim xlBook 
    As WorkbookxlBook2 As Workbook
    Dim xlSheet 
    As WorksheetxlSheet2 As Worksheet
    Dim CurrentForegroundThreadID 
    As Long
    'On Error GoTo ErrHandler
     
    Ano = Forms("DataInput")!InYear
    Mes = Forms("DataInput")!InMonth
    DateB = Mes & " " & "1, " & Ano
    MesPost = Month(DateB)
    AnoPos = Year(DateB)
    If MesPost = 12 Then
        MesPrevS = "1"
        AnoPrevS = AnoPos + 1
    Else
        MesPrevS = MesPost + 1
        AnoPrevS = AnoPos
    End If
    DateC = DateSerial(AnoPrevS, MesPrevS, 1)
    Datemo = DateC - 1
    MesRep = Format(DateC, "mmm")
    MesAnt = Mes & " " & "1, " & Ano
    over = DateDiff("d", DateB, Now())
    PastFile = False
    If over > 60 Then PastFile = True
    Path = "S:\GasInc\Call Centre\Billing Operations\Industrial Billing\Billing Status\Unbilled Report & Non billable profile report\Unbilled reports"
    Path = Path & " " & AnoPrevS & "\"
    NameF = Path & "*" & MesRep & "*.xl*"
    SetCurrentDirectoryA "S:\GasInc\Call Centre\Billing Operations\Industrial Billing\Billing Status\Unbilled Report & Non billable profile report\Unbilled reports\"
     
    '
    ---Open the excel file (named as the next month as contains prev month billedto be loading up in a variable array
    Set xlApp CreateObject("Excel.Application")
    xlApp.Visible True
    FileName 
    Path FName
    Set xlBook 
    xlApp.Workbooks.Open(FileNameUpdateLinks xlUpdateLinksNever)
    Set xlSheet xlBook.Worksheets(1)
    Debug.Print ActiveSheet.Name
    sheetname 
    ActiveSheet.Name
    '---Select Range of data and transfer to Array
    TemInst = Range("B5").Value
    TemClass = Range("D5").Value
    TemMI = Range("I5").Value
    Tempty = Range("B6").Value
    Call ValidateFormat(TemInst, TemClass, TemMI, Tempty)
    If Not OKFile Then Exit Sub
    Range("B7").Activate
    With xlSheet
        LastRow = .Cells.Find(What:="", after:=.Range("B8"), LookIn:=xlValues, LookAt:= _
                    xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
                    False, SearchFormat:=False).Row
    End With
    LastRow = LastRow - 1
    DataRange = "$B$7:" & "$I$" & LastRow
    TableBillData = Range(DataRange)
    xlBook.Close True
    xlApp.Quit              '
    addedd
    Set xlBook 
    Nothing
    Set xlSheet 
    Nothing
    Set xlApp 
    Nothing
    FName 
    Dir
    'Set xlApp = Nothing
    VarReturn = SysCmd(acSysCmdSetStatus, "Ricky is now looking for Move out")
    '
    Now it will looking for MOVE OUT inside the file JUST FOR CURRENT SITUATIONS
    '============================================================ NEW =================================
    MesRep1 = Format(Datemo, "mmm")
    NameF1 = Path & "*" & MesRep1 & "*.xl*"
    Set xlApp2 = CreateObject("Excel.Application")
    xlApp2.Visible = True


    FileName1 = Path & FName1
    Set xlBook2 = xlApp2.Workbooks.Open(FileName1, UpdateLinks = xlUpdateLinksNever)
    Set xlSheet2 = xlBook2.Worksheets(1)
    '
    Debug.Print ActiveSheet.Name
    'sheetname2 = ActiveSheet.Name
    '
    ---Select Range of data and transfer to Array for previous month file
    Range
    ("B7").Activate
    With xlSheet2
        LastRow2 
    = .Cells.Find(What:=""after:=.Range("B7"), LookIn:=xlValuesLookAt:= _
                    xlPart
    SearchOrder:=xlByColumnsSearchDirection:=xlNextMatchCase:= _
                    False
    SearchFormat:=False).Row
    End With
    LastRow2 
    LastRow2 1
    DataRange2 
    "$B$7:" "$I$" LastRow2
    Table2 
    Range(DataRange2)
    xlBook2.Close True
    Set xlBook2 
    Nothing
    Set xlSheet2 
    Nothing
    Set xlApp2 
    Nothing
    For 1 To LastRow2 6
        temp 
    Table2(j1)
        If 
    Table2(j8) < Datemo Then
            VarReturn 
    SysCmd(acSysCmdSetStatus" ")
            
    mook True
            found 
    False
            m 
    1
            
    Do While mook
                
    'm = 1
                If m <= LastRow - 6 Then
                    If TableBillData(m, 6) <> Table2(j, 6) Then
                        '
    Debug.Print m
                    
    Else
                        
    mook False
                        found 
    True
                    End 
    If
                Else
                    
    mook False
                    
    'found = False
                End If
                m = m + 1
            Loop
        Else
            mi2 = mi2 + 1
            '
    Debug.Print mi2
        End 
    If
        If 
    Not found Then
            
    If Table2(j3) <> "CUSE" Then MoveOutAcc MoveOutAcc 1
        End 
    If
        
    'Debug.Print "k="; k
        VarReturn = SysCmd(acSysCmdSetStatus, "Ricky is updating the Record: " & j - 1 & " of " & LastRow2 - 7)
    Next j
     
     
     
    MsgBox "Statistics for Transportation and Industrial Accounts have been Updated"
    VarReturn = SysCmd(acSysCmdSetStatus, " ")
    ErrHandler:
        Select Case Err
            Case 1004:    '
    Error 48 The Vlookup does not find a Premise so does not exist in the client base
            ErrMsg 
    "CRITICAL ERROR" vbCr "PLEASE OPEN VISUAL WINDOW AND PRESS RESET BUTTON" vbCr _
                   
    "Press OK to continue or " " IF NOTHING TO UPDATE THEN press Cancel"
            
    MsgBox ErrMsgvbCritical
            
    Case 94:
            
    ErrMsg "Nothing in the enter data fields" vbCr "Please post a Year and Month" vbCr _
                   
    "Press OK to continue or " " IF NOTHING TO UPDATE THEN press Cancel"
            
    MsgBox ErrMsgvbCritical
            
    'Case Else:
            '
    ErrMsg "Unknown Error" vbCr "Check all the data was updated" vbCr _
                   
    '"Press OK to continue or " & " IF NOTHING TO UPDATE THEN press Cancel"
            '
    MsgBox ErrMsgvbCritical
        End Select
    MsgBox 
    "Program Run completly"
    End Sub 

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I don't think you need to declare and open two Excel.Application. One instance of the application should be fine. Don't quit the app until both workbooks are closed. I did a test of very simple procedure and had no issues. So get the simple procedure working then expand in stages.

    Code:
    Sub OpenExcelTest()
    Dim xlApp As Excel.Application
    Dim xlBook As Workbook, xlBook2 As Workbook
    Dim xlSheet As Worksheet, xlSheet2 As Worksheet
    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Open("C:\Users\June\Forums\Book1.xlsx")
    Set xlSheet = xlBook.Worksheets(1)
    Set xlBook2 = xlApp.Workbooks.Open("C:\Users\June\Forums\Book2.xlsx")
    Set xlSheet2 = xlBook2.Worksheets(1)
    Debug.Print xlSheet.Range("A1")
    Debug.Print xlSheet2.Range("A1")
    xlBook.Close True
    xlBook2.Close True
    xlApp.Quit
    Set xlBook = Nothing
    Set xlSheet = Nothing
    Set xlBook2 = Nothing
    Set xlSheet2 = Nothing
    Set xlApp = Nothing
    End Sub
    You should have 'Option Explicit' at the top of each code module and then declare every variable.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Ricardo Caicedo is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Aug 2014
    Posts
    11
    It works you guys are awesome thanks June7

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 06-25-2015, 03:56 PM
  2. Replies: 4
    Last Post: 05-15-2014, 12:49 PM
  3. Need help opening/closing Excel files
    By djrickel in forum Programming
    Replies: 1
    Last Post: 03-25-2014, 08:45 AM
  4. Error with macro while opening access.
    By Trisha in forum Access
    Replies: 5
    Last Post: 03-04-2014, 01:07 PM
  5. Replies: 4
    Last Post: 06-14-2011, 07:19 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums