Jagwarman
asked on
VBA to open a file from a folder where year and date changes
Could an expert provide me with the code to open a file from a folder where year and date changes.
path is G:\Sales\Recon\Results\201 4\dd.mm.yy yy.xls
where 2014 will become 2015 and the file will be yesterdays date.
Thanks
path is G:\Sales\Recon\Results\201
where 2014 will become 2015 and the file will be yesterdays date.
Thanks
Similar to above:
Sub OpenFile()
FileYear = Year(Date)
FileDate = Format(Date - 1,"dd.mm.yyyy")
FilePath = "G:\Sales\Recon\Results\" & FileYear & "\" & FileDate &".xls"
Workbooks.Open (FilePath)
End Sub
Sub OpenFile()
FileYear = Year(Date)
FileDate = Format(Date - 1,"dd.mm.yyyy")
FilePath = "G:\Sales\Recon\Results\" & FileYear & "\" & FileDate &".xls"
Workbooks.Open (FilePath)
End Sub
Sub Openfile()
Dim oExcel As Excel.Application
Dim oWB As Workbook
Dim strFile As String
On Error GoTo Error_Openfile
strFile = "o:\" & Format(Now(), "yyyy") & "\" & Format(Now() - 1, "dd.mm.yyyy") & ".xlsx"
Set oExcel = New Excel.Application
oExcel.Visible = True
Set oWB = oExcel.Workbooks.Open(strF ile)
Exit Sub
Error_Openfile:
If Not oWB Is Nothing Then oWB.Close
Set oWB = Nothing
Set oExcel = Nothing
End Sub
Dim oExcel As Excel.Application
Dim oWB As Workbook
Dim strFile As String
On Error GoTo Error_Openfile
strFile = "o:\" & Format(Now(), "yyyy") & "\" & Format(Now() - 1, "dd.mm.yyyy") & ".xlsx"
Set oExcel = New Excel.Application
oExcel.Visible = True
Set oWB = oExcel.Workbooks.Open(strF
Exit Sub
Error_Openfile:
If Not oWB Is Nothing Then oWB.Close
Set oWB = Nothing
Set oExcel = Nothing
End Sub
ASKER
All thanks for the replies:
aebea not sure why but yours returns strDay as 01.1.2014
One thing I did forget to say was that if there is a weekend, on the Monday I need the macro to open the file from the friday. Can they be amended to do this?
Thanks
aebea not sure why but yours returns strDay as 01.1.2014
One thing I did forget to say was that if there is a weekend, on the Monday I need the macro to open the file from the friday. Can they be amended to do this?
Thanks
ASKER
Sorry, one more question/request.
The teams have thrown me a curveball and this may not be possible.
they save their files in folders by year by month and for each month they give it a number, so January folder is 01.Jan 2014 Feb is 02.Feb 2014 March is 03.Mar 2014 etc
so the path I originally gave needs amendment to
G:\Sales\Recon\Results\201 4\01.Jan 2014\filename dd.mm.yyyy.xls
apologies
The teams have thrown me a curveball and this may not be possible.
they save their files in folders by year by month and for each month they give it a number, so January folder is 01.Jan 2014 Feb is 02.Feb 2014 March is 03.Mar 2014 etc
so the path I originally gave needs amendment to
G:\Sales\Recon\Results\201
apologies
A curveball to throw back would be to suggest that the use of fullstops within file names and possibly folder names should be avoided.
In filenames some AV software don't like the us of Fullstops as it suggests multiple extensions.
For more reliable sort ordering I have found the use of naming convention of yyyymmdd to be more reliable. This files will always be in chronological order.
However, your current requirement includes fullstops so will owrk on that accordingly.
Thanks
Rob
In filenames some AV software don't like the us of Fullstops as it suggests multiple extensions.
For more reliable sort ordering I have found the use of naming convention of yyyymmdd to be more reliable. This files will always be in chronological order.
However, your current requirement includes fullstops so will owrk on that accordingly.
Thanks
Rob
Also, what happens on first day of month and/or first day of year?
I assume, first of month will require last day of previous month which will be in previous month folder.
Likewise, first of year will require last day of previous month which will be in December folder of previous year.
Thanks
Rob
I assume, first of month will require last day of previous month which will be in previous month folder.
Likewise, first of year will require last day of previous month which will be in December folder of previous year.
Thanks
Rob
I assume you are running this macro from within an existing Excel file.
Would it be feasible to construct a filename and path using a formula within that file and then use that as the location of the workbook to open?
Thanks
Rob H
Would it be feasible to construct a filename and path using a formula within that file and then use that as the location of the workbook to open?
Thanks
Rob H
See attached with formula calculation of filename and button that will then open the filename constructed.
Thanks
Rob H
Scratch this comment, didn't allow for all eventualities; working on that now.
Thanks
Rob H
Scratch this comment, didn't allow for all eventualities; working on that now.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Or something like this:
Sub Openfile(strFilename As String)
Dim oExcel As Excel.Application
Dim oWB As Workbook
Dim strFile As String
On Error GoTo Error_Openfile
strFile = "G:\Sales\Recon\Results\20 14\" & Format(Now() - 1, "dd.mmm.yyyy") & "\" & strFilename & " " & _
Format(Now(), "yyyy") & "\" & Format(Now() - 1, "dd.mm.yyyy") & ".xls"
Set oExcel = New Excel.Application
oExcel.Visible = True
Set oWB = oExcel.Workbooks.Open(strF ile)
Exit Sub
Error_Openfile:
If Not oWB Is Nothing Then oWB.Close
Set oWB = Nothing
Set oExcel = Nothing
End Sub
Sub Openfile(strFilename As String)
Dim oExcel As Excel.Application
Dim oWB As Workbook
Dim strFile As String
On Error GoTo Error_Openfile
strFile = "G:\Sales\Recon\Results\20
Format(Now(), "yyyy") & "\" & Format(Now() - 1, "dd.mm.yyyy") & ".xls"
Set oExcel = New Excel.Application
oExcel.Visible = True
Set oWB = oExcel.Workbooks.Open(strF
Exit Sub
Error_Openfile:
If Not oWB Is Nothing Then oWB.Close
Set oWB = Nothing
Set oExcel = Nothing
End Sub
ASKER
robhenson thye path it is opening is G\SalesaRecon\2014\14.01.2 014.xls
It is missing the 01.Jan 2014
G:\Sales\Recon\Results\201 4\01.Jan 2014\filename dd.mm.yyyy.xls
It is missing the 01.Jan 2014
G:\Sales\Recon\Results\201
Which VBA Script are you looking at?
Script above or in attached file?
Script above was prior to Month folder requirement so can be ignored. In file there are 2 scripts, OpenFile or OpenFile2
Should be using the OpenFile2 script, or the button on the sheet.
Thanks
Rob
Script above or in attached file?
Script above was prior to Month folder requirement so can be ignored. In file there are 2 scripts, OpenFile or OpenFile2
Should be using the OpenFile2 script, or the button on the sheet.
Thanks
Rob
ASKER
Must have been something I was doing the 'file' OpenFile2 script works fine. Thanks
Open in new window