Excelexcel86
Board Regular
- Joined
- Feb 28, 2023
- Messages
- 99
- Office Version
- 365
- Platform
- Windows
- Mobile
Hi I have the following code below on multiple sheets in excel where only 1 piece of code changes which is the path as my data is in different folders what I want to know is is there a piece of code I can put into all my sheets so when run it opens in the current sheet because at the moment it opens up another workbook for each one .
Option explicit
Sun openlatestfile
Dim mypath as string
Dim myfile as string
Dim latestfile as string
Dim latestdate as date
The below path is an example
Mypath = “s:\jobs\area\people\”
If right (mypath, 1) <> “\” then mypath = mypath & “\”
Myfile = dir (mypath & “*.csv”,vbnormal)
If len (myfile) = 0 then
Exit sub
End if
Do while len (myfile) > 0
lmd= filedatetime (mypath & myfile)
If lmd > latestdate then
latestfile = myfile
latestdate = lmd
End if
myfile = dir
Loop
workbooks.open mypath & latestfile
End sub
That’s my code the only bit that changes for every sheet is mypath =
Option explicit
Sun openlatestfile
Dim mypath as string
Dim myfile as string
Dim latestfile as string
Dim latestdate as date
The below path is an example
Mypath = “s:\jobs\area\people\”
If right (mypath, 1) <> “\” then mypath = mypath & “\”
Myfile = dir (mypath & “*.csv”,vbnormal)
If len (myfile) = 0 then
Exit sub
End if
Do while len (myfile) > 0
lmd= filedatetime (mypath & myfile)
If lmd > latestdate then
latestfile = myfile
latestdate = lmd
End if
myfile = dir
Loop
workbooks.open mypath & latestfile
End sub
That’s my code the only bit that changes for every sheet is mypath =
