Click-Click,Bang,Bang,Bang,Done

[emjay]

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

copy *_output.xls output.xls

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:

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