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

AlexB123

Board Regular
Joined
Dec 19, 2014
Messages
207
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".

Code:
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

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]wwrs[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD][TABLE="width: 254"]
<colgroup><col></colgroup><tbody>[TR]
[TD]C:\DailyFiles\TPS_050818_WRS.xls[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[/TR]
[TR]
[TD]wrs2
[/TD]
[TD][TABLE="width: 254"]
<colgroup><col></colgroup><tbody>[TR]
[TD]C:\DailyFiles\TPS_050818_RS2.xls[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[/TR]
[TR]
[TD]wrs3
[/TD]
[TD][TABLE="width: 254"]
<colgroup><col></colgroup><tbody>[TR]
[TD]C:\DailyFiles\TPS_050818_RS3.xls[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[/TR]
[TR]
[TD]wawrs<strike></strike>
[/TD]
[TD][TABLE="width: 254"]
<colgroup><col></colgroup><tbody>[TR]
[TD]C:\DailyFiles\TPS_050418Auto_WRS.xls[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[/TR]
[TR]
[TD]wars2<strike></strike>
[/TD]
[TD][TABLE="width: 254"]
<colgroup><col></colgroup><tbody>[TR]
[TD]C:\DailyFiles\TPS_050718Auto_RS2.xls[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[/TR]
[TR]
[TD]wars3<strike></strike>
[/TD]
[TD][TABLE="width: 254"]
<colgroup><col></colgroup><tbody>[TR]
[TD]C:\DailyFiles\TPS_050718Auto_RS3.xls[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
NEVERMIND!!!

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

Cheers!
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,212
Members
452,618
Latest member
Tam84

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top