Need some help in trying to get several steps to be performed in one shot and can't seem to get them to work together.
Using Excel 2007
I have a "data" file that is in this format: (tab delimited)
yyyymmdd_Output.xls
ie.
20090327_Output.xls
(I currently use a .bat file
to make a copy of this file and rename it as Output.xls - and then move the dated file to an archive folder (in the current directory - because I can't hardcode a directory I try to drop it into the same folder as the bat file) because I havent figured out how to do that in VBA yet, ideas?
I have a "report" file in this format:
Fuel Priceformulas.xlsm
The report file has all the formulas and calculations to correctly snag the data and generate the "report" by saving it off in this filename format:
Fuel Price Report yyyymmdd.xlsx
ie.
Fuel Price Report 20090329.xlsx
see below:
I THINK this will work and always drop the dated Fuel Price Report yyyymmdd.xlsx in the same directory as the Fuel Priceformulas.xlsm which holds this macro, right?
Anyone think this could be easier?
Ideally I'd like to arrange the following.
1) User starts .bat or Opens Excel file from any directory (Hoping to move this to a network folder, but for now will be local/random directory)
2) Either a rename of dated file occurs or Fuel Priceformulas.xlsm forumula is updated to read whatever .xls file is present
3) Fuel Price Report yyyymmdd.xlsx is generated
4) Fuel Price Report yyyymmdd.pdf is generated using only a portion of the first worksheet in Fuel Priceformulas.xlsm (ie. (A5:H30) with maybe some other formatting
Your thoughts? I can't seem to cobble together various different VBA snippets I'm finding into a cohesive unit.
-MJ
Using Excel 2007
I have a "data" file that is in this format: (tab delimited)
yyyymmdd_Output.xls
ie.
20090327_Output.xls
(I currently use a .bat file
Code:
copy *_output.xls output.xls
I have a "report" file in this format:
Fuel Priceformulas.xlsm
The report file has all the formulas and calculations to correctly snag the data and generate the "report" by saving it off in this filename format:
Fuel Price Report yyyymmdd.xlsx
ie.
Fuel Price Report 20090329.xlsx
see below:
Code:
Sub Copy_ActiveSheet_1()
'Working in Excel 97-2007
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set Sourcewb = ActiveWorkbook
'Copy the sheet to a new workbook
ActiveSheet.Copy
Set Destwb = ActiveWorkbook
'Determine the Excel version and file extension/format
With Destwb
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007
'We exit the sub when your answer is NO in the security dialog that you
'only see when you copy a sheet from a xlsm file with macro's disabled.
If Sourcewb.Name = .Name Then
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
MsgBox "Your answer is NO in the security dialog"
Exit Sub
Else
FileExtStr = ".xlsx": FileFormatNum = 51
End If
End If
End With
' 'Change all cells in the worksheet to values if you want
' With Destwb.Sheets(1).UsedRange
' .Cells.Copy
' .Cells.PasteSpecial xlPasteValues
' .Cells(1).Select
' End With
' Application.CutCopyMode = False
'Save the new workbook and close it
'TempFilePath = Application.DefaultFilePath & "\"
TempFilePath = CurDir & "\"
TempFileName = "Fuel Price Report " & Format(((Now) - 1), ("yyyymmdd"))
With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
.Close SaveChanges:=False
End With
'MsgBox "You can find the new file in " & Application.DefaultFilePath
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
I THINK this will work and always drop the dated Fuel Price Report yyyymmdd.xlsx in the same directory as the Fuel Priceformulas.xlsm which holds this macro, right?
Anyone think this could be easier?
Ideally I'd like to arrange the following.
1) User starts .bat or Opens Excel file from any directory (Hoping to move this to a network folder, but for now will be local/random directory)
2) Either a rename of dated file occurs or Fuel Priceformulas.xlsm forumula is updated to read whatever .xls file is present
3) Fuel Price Report yyyymmdd.xlsx is generated
4) Fuel Price Report yyyymmdd.pdf is generated using only a portion of the first worksheet in Fuel Priceformulas.xlsm (ie. (A5:H30) with maybe some other formatting
Your thoughts? I can't seem to cobble together various different VBA snippets I'm finding into a cohesive unit.
-MJ