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".
[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]
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]