I have workbook A open. In A I have a macro that has to call a macro (New_week) in workbook B.
Problem is that I have only variables as name for B.
Here is the script of the macro in book A.
Sub Downtime_ASL()
'
' Downtime_ASL Macro
'
Dim Mypath As String
Dim Myfile As String
Dim LatestFile As String
Dim LatestDate As Date
Dim GL As Date
Mypath = "I:\Excel\VBA project downtime\ASL"
If Right(Mypath, 1) <> "\" Then Mypath = Mypath & "\"
Myfile = Dir(Mypath & "Downtime*.xlsm", vbNormal)
If Len(Myfile) = 0 Then
MsgBox "Nothing found...", vbExclamation
Exit Sub
End If
Do While Len(Myfile) > 0
GL = FileDateTime(Mypath & Myfile)
If GL > LatestDate Then
LatestFile = Myfile
LatestDate = GL
End If
Myfile = Dir
Loop
Workbooks.Open Mypath & LatestFile
Application.Run "'Mypath & LatestFile'!New_week"
End Sub
In the directory where B is located there are a lot of files. This macro looks for the last saved file and opens it.
In workbook B there is a macro "New_week" that I want to call.
The workbook will be opened, so the variables are known.
But I get errors when I get to the line Application.Run "'Mypath & LatestFile'!New_week".
The error says "In c:\users\documents\ there is nof file "Mypath & LatestFile.xls"
What did I do wrong ?
Problem is that I have only variables as name for B.
Here is the script of the macro in book A.
Sub Downtime_ASL()
'
' Downtime_ASL Macro
'
Dim Mypath As String
Dim Myfile As String
Dim LatestFile As String
Dim LatestDate As Date
Dim GL As Date
Mypath = "I:\Excel\VBA project downtime\ASL"
If Right(Mypath, 1) <> "\" Then Mypath = Mypath & "\"
Myfile = Dir(Mypath & "Downtime*.xlsm", vbNormal)
If Len(Myfile) = 0 Then
MsgBox "Nothing found...", vbExclamation
Exit Sub
End If
Do While Len(Myfile) > 0
GL = FileDateTime(Mypath & Myfile)
If GL > LatestDate Then
LatestFile = Myfile
LatestDate = GL
End If
Myfile = Dir
Loop
Workbooks.Open Mypath & LatestFile
Application.Run "'Mypath & LatestFile'!New_week"
End Sub
In the directory where B is located there are a lot of files. This macro looks for the last saved file and opens it.
In workbook B there is a macro "New_week" that I want to call.
The workbook will be opened, so the variables are known.
But I get errors when I get to the line Application.Run "'Mypath & LatestFile'!New_week".
The error says "In c:\users\documents\ there is nof file "Mypath & LatestFile.xls"
What did I do wrong ?