Linked Import Table and associated Excel Macro not Choosing Correct Files to bring in to Access

[AlexB123]

Hi all,

I have a form that chooses files on a network based on the day they were delivered. Form_Load() for frmImport uses if-elseif-else statement to calculate which day each file should look back to. Most look back to the previous business day, but three look two days back. In this database, I have had to change the way excel macro workbooks are called, and I used the "late binding approach." In the macro that is called, it takes the files and copies the contents to my linked import table. The problem is the macro workbook is using the wrong day for the first of the three files, while the other two files are looking back correctly (i.e., if today is 5/9, the first is now looking to 5/4, the other two to 5/7). Is this related to my late binding approach? Is the variable that assigns the back1day or back2days getting overwritten? Code below. Many thanks!

Note that "r10" and other variables are access text box controls that display whether the import process is "ready" or completed, "Imported".

Private Sub Form_Load()
If Weekday(Date) = 2 Then 'mon
    d1 = Date - 3
    d2 = Date - 4
ElseIf Weekday(Date) = 3 Then 'tues
    d1 = Date - 1
    d2 = Date - 4
Else 'wed-fri
    d1 = Date - 1
    d2 = Date - 2
End If
back1day = d1
back2days = d2
fWwrs = "C:\DailyFiles\TPS_" & Format(back1day, "MMDDYY") & "_WRS.xls"
fWrs2 = "C:\DailyFiles\TPS_" & Format(back1day, "MMDDYY") & "_RS2.xls"
fWrs3 = "C:\DailyFiles\TPS_" & Format(back1day, "MMDDYY") & "_RS3.xls"
fWAwrs = "C:\DailyFiles\TPS_" & Format(back2days, "MMDDYY") & "Auto_WRS.xls"
fWArs2 = "C:\DailyFiles\TPS_" & Format(back2days, "MMDDYY") & "Auto_RS2.xls"
fWArs3 = "C:\DailyFiles\TPS_" & Format(back2days, "MMDDYY") & "Auto_RS3.xls"
End Sub
Private Sub Command74_Click()
 Dim xlApp As Object
    Dim xlWB As Object
    Dim xlSheet As Object
    Set xlApp = CreateObject("Excel.Application")
 Set xlWB = xlApp.Workbooks.Open("C:\DBfiles\LMacros.xlsm")
    Set xlSheet = xlWB.Worksheets(1)
    xlApp.Visible = False
    xlApp.Application.DisplayAlerts = False
 
 'autowrs
        If r10 = "Ready" Then
        ElseIf r10 = "Imported" Then
            xlSheet.Range("B10").Value = fWAwrs
  ElseIf r10 = "Imported" Then        
   xlSheet.Range("B10").Value = "NO!"
        ElseIf IsNull(r10) Then
            xlSheet.Range("B10").Value = "NO!"
  EndIf
'run macro and stuff!
    xlWB.Save
    xlApp.Run "PulltheGoods"
    DoEvents
    xlApp.Quit
    Set xlApp = Nothing
    Set xlWB = Nothing
    Set xlSheet = Nothing 
End Sub
Private Sub PullTheGoods()
Dim fwWRS, fwRS2, fwRS3, fwaWRS, fwaRS2, fwaRS3 As String
fwWRS = Range("b7").Value
fwRS2 = Range("b8").Value
fwRS3 = Range("b9").Value
fwaWRS = Range("b10").Value
fwaRS2 = Range("b11").Value
fwaRS3 = Range("b12").Value
'More Code to copy the necessary data from source files.
End Sub

wwrs <colgroup><col width="64" style="width:48pt"> </colgroup><tbody> </tbody> <strike></strike>	C:\DailyFiles\TPS_050818_WRS.xls <colgroup><col></colgroup><tbody> </tbody> <strike></strike>
wrs2	C:\DailyFiles\TPS_050818_RS2.xls <colgroup><col></colgroup><tbody> </tbody> <strike></strike>
wrs3	C:\DailyFiles\TPS_050818_RS3.xls <colgroup><col></colgroup><tbody> </tbody> <strike></strike>
wawrs<strike></strike>	C:\DailyFiles\TPS_050418Auto_WRS.xls <colgroup><col></colgroup><tbody> </tbody> <strike></strike>
wars2<strike></strike>	C:\DailyFiles\TPS_050718Auto_RS2.xls <colgroup><col></colgroup><tbody> </tbody> <strike></strike>
wars3<strike></strike>	C:\DailyFiles\TPS_050718Auto_RS3.xls <colgroup><col></colgroup><tbody> </tbody> <strike></strike>

<tbody>
</tbody>

 

[AlexB123]

NEVERMIND!!!

As soon as I posted I saw that my two lines of my code had gotten switched.

Cheers!