Click-Click,Bang,Bang,Bang,Done

emjay

New Member
Joined
Mar 31, 2009
Messages
1
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
Code:
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:

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
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,643
Members
414,083
Latest member
Mrsash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top