Macro for other files

Topic

New Reply

I frequently download files from the internet that are created for me. However, once I get them, I want to make several changes in the formatting and content. I can write the macro to make the changes but of course the macro won’t be in the new file that I download from my resource. Can I create a macro in a separate file that I just run and maybe it asks me the name of the file I want to manipulate and then it will transform my recently downloaded file into the way I want it to look? That way I can keep my ‘macro’ spreadsheet and update that one macro as needed but apply it to all the sheets I download. I just don’t know how to apply a macro to another file or for the macro to know which file to do the work on. Alternately, I guess I can download a new spreadsheet, copy all the data, paste it into my template file, run the macro, and then save it with a different file name. I’m thinking that isn’t as efficient as I’d like it to be…Thanks for any ideas to point me in the right direction.

Reply | Quote

Replies

JP,

You can place the macro in your Personal.xlsm file then it will be available to any workbook you open.
The Personal.xlsm file is located in this directory:
[noparse]
C:Users[/noparse]Your UserID HereAppDataRoamingMicrosoftExcelXLSTART

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Cool. So I can open the worksheet I download and run the macro right from there because it will be able to see the macro in that location? That will do the trick. Thanks so much…

Reply | Quote

I guess I need a little more help. I have a xlsm file open and recorded a macro in it just to get my personal.xlsm file started but when I go to the path you suggested, I see a lot of other files there that have macros in them but I don’t see one there named personal or XLSTART. Do I need to create that file before trying to record a macro in it? When I go to record a macro, I choose to Store in Personal Macro Workbook. What am I missing?

Reply | Quote

OK, thanks again. That should be easy enough.

Reply | Quote

JP,

Create a file with the macro in it then save it to the location I specified as Personal.xlsm.

Here’s a MS Article on the topic?

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

jpzinn,

If RG’s solution does not work for you, here is unique alternative measure using a Class that monitors for the opening of a workbook. When detected, it will run any code you place in the WBdetected routine.

In a Class module named MonitorNewWB, place the following code:

Code:

 Public WithEvents App As Application

 Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
    WBdetected Wb
 End Sub


 Private Sub Class_Terminate()
     Set App = Nothing
 End Sub

In a standard module, place the following code:

Code:

 Sub InitApp()
     [COLOR=”#008000″]’MUST BE INITIATED IN WORKBOOK_OPEN OR AFTER AN ERROR STATE[/COLOR]
     Set Watcher = New MonitorNewWB
         Set Watcher.App = Application
 End Sub


Sub WBdetected(wb2 As Workbook)
    [COLOR=”#008000″]’YOUR CODE GOES HERE[/COLOR]
Exit Sub

In the ThisWorkbook module, place the following code:

Code:

Private Sub Workbook_Open()
    InitApp
End Sub

In the WBdetected routine, add your code that formats the newly opened workbook. Save the project with any name then re-open. The class will be initialized from the workbook_open event routine and the monitoring will start for the opening of another workbook. When detected, the code you placed in the WBdetected routine will run and format the new workbook.

Keep in mind that when monitoring begins, it will apply your code to any workbook opened so if downloaded workbook has some type of naming convention, check the name and exit if the name rule is not met. You can forego the check but just make sure that the downloaded workbook is the next workbook opened. If there is any error state, the InitApp routine will have to be run to restart the monitoring again so add it to your error handling routine.

As a guideline, here is some code I run in the WBdetected.

Code:

Sub WBdetected(wb2 As Workbook)
Application.ScreenUpdating = False
Dim wb1 As Workbook
Dim LastRow As Integer, EndRow As Integer
If wb2.Name = “IRTv1.1.xlsm” Then Exit Sub
If wb2.Name  “CwReport.xls” Then GoTo errorhandler
Set wb1 = ThisWorkbook
If wb1.Worksheets(1).FilterMode = True Then
    wb1.Worksheets(1).Range(“A2:F2”).AutoFilter
End If
LastRow = wb1.Worksheets(1).Cells(Rows.Count, 2).End(xlUp).Row + 1
EndRow = wb2.Worksheets(1).Cells(Rows.Count, 2).End(xlUp).Row + 1
For I = 3 To EndRow
    For J = 2 To 7
        If J = 2 Or J = 7 Then
            wb1.Worksheets(1).Cells(LastRow, J – 1) = CDate(wb2.Worksheets(1).Cells(I, J))
            wb1.Worksheets(1).Cells(LastRow, J – 1).HorizontalAlignment = xlLeft
        Else:
            wb1.Worksheets(1).Cells(LastRow, J – 1) = wb2.Worksheets(1).Cells(I, J)
            wb1.Worksheets(1).Cells(LastRow, J – 1).HorizontalAlignment = xlLeft
        End If
    Next J
    LastRow = LastRow + 1
Next I
Application.DisplayAlerts = False
wb2.Close
Application.DisplayAlerts = True
FormatSheet
Exit Sub
errorhandler:
MsgBox “The wrong workbook has opened”
End Sub

If you decide to give this a go, let me know if you need any help adding your code to the WBdetected routine.

HTH,
Maud

Reply | Quote

Maud,

Nice code!

Just to be clear on the operation…You have to run the workbook with your code in it every time before you start to download/open the files to be processed?

When you close that workbook/or exit Excel … then when you open another workbook/or restart Excel or the function will NOT be active?

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thanks RG,

Yes, you start the workbook with the code. It just sits there and waits for another workbook to open. When one does, whatever code in the WBdetected will launch. The workbook object will be passed to the code as wb2 so the name of the workbook that just opened can be extracted by wb2.Name. You can check the name against a specific name to confirm whether or not the correct workbook has opened to either continue with the code or exit the sub. Additional code could be added to save and close the newly opened workbook after operations have been performed and optionally to close the main workbook with the code (monitoring stops) or leave it open and continue monitoring. Your choice.

Whatever you want to code in the WBdetected routine will execute when another workbook opens.

HTH,
Maud

Reply | Quote

RG,

One other thing I forgot to mention is that you do not want the code to run when you first open the file with the code, only on the opening of subsequent excel files. So what ever code you put in the WBdetected should include a check to see if it is the main file that has just opened. For example, if the monitoring file is called “MonitorExcel.xlsm” then the WBdetected routine should include the line:

If wb2.Name = “MonitorExcel.xlsm” Then Exit Sub

The monitoring will pick up the opening of the main workbook itself and fire the code. Bypass the execution by checking if it is the “MonitorExcel.xlsm” file that just opened or whatever you happen to name it.

Don’t forget to initialize the monitoring in the workbook_open and error handling.

Thanks,
Maud

Reply | Quote

Maud,

I can not seem to get this to work? I know I’m doing something wrong but can’t seem to figure it out.

I added a End Sub to the routing with the Exit Sub since that wouldn’t compile.

I’ve also added a test for the WB containing the code.

Take a look and tell me what glaringly obvious thing I missed!

My Test File: 46980-Excel-VBA-Monitor-for-Workbook-open-and-run-code

Cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

RG,

No modifications needed except in WBdetected routine. I neglected to copy the dim statements into my post (my bad!).

Code:

[COLOR=”#0000FF”] Dim Watcher As MonitorNewWB[/COLOR]

 Sub InitApp()
     ‘MUST BE INITIATED IN WORKBOOK_OPEN OR AFTER AN ERROR STATE
     Set Watcher = New MonitorNewWB
         Set Watcher.App = Application
 End Sub

Sub WBdetected(wb2 As Workbook)
If wb2.Name = “Sample Monitoring.xlsm” Then Exit Sub
MsgBox “Hello”
End Sub

Start Sample Monitoring.xlsm and at some point, open another workbook. You should see a msgbox display “Hello”

Reply | Quote

Maud,

Upon further expermination with your code I’ve discovered the following:

You can make the code generic, as to the name of the workbook containing it, by adding…

To ThisWorkBook Module

Code:

Option Explicit

Private Sub Workbook_Open()

    [COLOR="#0000FF"]zMyName = ActiveWorkbook.Name[/COLOR]
    InitApp
	
End Sub

To the Standard Module

Code:

Option Explicit

 [COLOR="#0000FF"]Public zMyName As String[/COLOR]
 Dim Watcher    As MonitorNewWB
 
 Sub InitApp()
     'MUST BE INITIATED IN WORKBOOK_OPEN OR AFTER AN ERROR STATE
     Set Watcher = New MonitorNewWB
         Set Watcher.App = Application
 End Sub


Sub WBdetected(wb2 As Workbook)

   [COLOR="#0000FF"] If (ActiveWorkbook.Name = zMyName) Then Exit Sub[/COLOR]

    'YOUR CODE GOES HERE
    MsgBox "I RAN!"
    
   Exit Sub
   
End Sub

If you open another instance of Excel (Hold Shift while double clicking an Excel file in Explorer) it is detected.

If you open another file by right clicking on the Excel icon and then selecting from the Jump List (with or without holding Shift) it is not detected until you click on the Excel window, seems Excel looses focus and won’t run the code until it regains focus. Strangely this happens in Excel 2010 running Win 10 but not on Excel 2003 running Win 7?

You must declare the Public variable in a standard module! It will not work if declared in the ThisWorkbook module.

Tested Excel 2010 & 2003.

Thanks again Maud this is a great piece of code it is going straight into my toolbox as I can see many uses for it.

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Classic example of how tweaks can make a good thing even better. The workbook being monitored is generic however you have taken it to the next level. I have so many applications for this technique at work since many systems export data as an opened Excel file.

Again, thanks for the tweaks. They will be in my distributions.

Maud

Reply | Quote

Nice work RG & Maudibe

I guess I can download a new spreadsheet, copy all the data, paste it into my template file, run the macro, and then save it with a different file name.

That’s what I do with a lot of Word and some Excel files. I tried for a more auto solution [but nothing as good as the guys cooked up above], but dropped it after some annoying gotchas—eg in Word a particular style wouldn’t co-operate and had to be individually fixed, and in Excel I found date fields to be too flaky.

RG & Maudibe, if I understand your code correctly, it’ll operate on any Excel file which opens. So it would work on CSVs or text files too, as long as they were opened with Excel, right?

For people who wouldn’t want such code running on all opened files, is there a way to have Excel monitor a specific folder path, and run a routine on any files found there? If so, one could move applicable files into this special folder, while leaving all other files untouched.

I know I’ve seen code/app for monitoring folders ‘somewhere’… and I ‘know’ there’s code which will cycle thru a bunch of files in a folder—opening each one, running code, saving and closing, and on to the next file. It would be neat if such a monitor ran say hourly or daily, save having to open the new files manually.

Lugh.
~
Alienware Aurora R6; Win10 Home x64 1803; Office 365 x32
i7-7700; GeForce GTX 1060; 16GB DDR4 2400; 1TB SSD, 256GB SSD, 4TB HD

Reply | Quote

Hi Lugh,

Yes, it will work with anything that opens with Excel. You could easily add a line to the WDdetected routine that checks the path of file then exits the routine if not from a specific folder before opening it.

It would be neat if such a monitor ran say hourly or daily, save having to open the new files manually.

This could be done with Windows Task Scheduler or Application.Ontime in VBA.

Maud

Reply | Quote

Wow, you guys took my original question to a completely different level. For my purposes, the macro in Personal Macros is a good solution. However, I have run into another snag to which you will probably know the solution.
Sometimes when I try to copy data from one file to the new one, I want to use PasteSpecial for formulas or column widths, etc. The problem is that I always get a run time error 1004 and a message saying the PasteSpecial method of the range object failed (or something to that effect). From what I’ve read, it may be because after I copy the material, instead of activating the file where I’m going to paste it, I close the one from which I’m copying (therefore going back to the place I’m going to paste it). Is that the problem? If so, do I need to just leave the file open and refer to the two files by their names?

In psuedo code –
originalFile – copy range (A1:BU3)
close originalFile
Select A1:BU3 and PasteSpecial

always generates an error

Reply | Quote

JP,

Don’t close the source file until AFTER you do the copy.

Here’s how I do it. There are shorter ways but I like this as it is very clear, at least to me, what is going on.

Code:

Option Explicit

Sub TransferValues()

   Dim shtCopyFrom As Worksheet
   Dim shtPasteTo  As Worksheet
   Dim wkbSource   As Workbook
   Dim wkbDest     As Workbook
   Dim zSourceRng  As String

   Application.ScreenUpdating = False

'*** Setup ***

   Set wkbSource = ActiveWorkbook
   zSourceRng = Range("B7:H17").Address()   '*** Set Source range here! ***
   '*** Replace d:pathfilename.ext below ***
   Set wkbDest = Application.Workbooks.Open(Filename:="D:PATHFILENAME.EXT")
   Set shtCopyFrom = wkbSource.Worksheets("Sheet1") '*** Source Sheet ***
   Set shtPasteTo = wkbDest.Worksheets("Sheet3")    '*** Dest   Sheet ***

'*** Execution ***

   shtCopyFrom.Range(zSourceRng).Copy
'*** Pasting Values to first unused row in list ***
   shtPasteTo.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues

'*** Clean Up ***
   Application.CutCopyMode = False
   Application.ScreenUpdating = True
   wkbDest.Close True

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thanks HTH. I’ll definitely give that a try tomorrow. One question though is that each time I run this, it is going to be a different file name where I want to paste stuff. The one I copy from will always be the same. Is there a way for me to get the name of the workbook where I am going to paste stuff via the VBA? That will always be the file that is open and from where I run the macro. I guess I’m looking for something like – ‘Set wkbDest = the current file that is open’ in place of your line of code that says, ‘ Set wkbDest = Application.Workbooks.Open(Filename:=”D:PATHFILENAME.EXT’
I think that might be my last hurdle unless it doesn’t copy formulas but only copies the values…I know I can’t use xlPasteValues when I need to use xlPasteFormulas.

Reply | Quote

Thanks HTH….

jpzinn,

HTH = “Hope that helps” but feel free to call him RG

Reply | Quote

JP,

Code:

Option Explicit
'                        +--------------------+                 +----------+
'------------------------|   GetFileToOpen()  |-----------------| 01/15/14 |
'                        +--------------------+                 +----------+
'Called by  :
'Arguments  : zSelected - a String array declared empty & ReDimed to 1
'             zExts     - a list of allowed extensions for the filter
'                         Ex: "*.xlsx, *.xls, *.xlsm, *.xlsb"
'                         Note: Only Excel filetypes as function is written!
'             zMulti    - True allows multi select, False allows single select.
'             zFileFilter - Optional - used to limit the files shown by name
'                         pattern, EX: "CA*.xls*" if ommited "*.xls*" will
'                         be used. Note: using "*.*" will over ride the zExts
'                         filter! You can also specify a drive/path to set
'                         the initial folder displayed.
'Notes      : You can uncomment the .Title line and supply your own
'             dialog box title and add an argument if you want to pass it!
'             You can uncomment the .ButtonName to supply a custom OK button
'             caption which can also be passed by argument if desired.


Function GetFileToOpen(ByRef zSelected, zExts As String, bMulti As Boolean, _
                       Optional zFileFilter As Variant) As Long

    Dim fd             As FileDialog
    Dim lCnt           As Long
    
    If IsMissing(zFileFilter) Then zFileFilter = "*.xls*"
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .Filters.Clear   '*** Clear old filters just precautionary ***
        .Filters.Add "Spreadsheets", zExts, 1
        .InitialFileName = zFileFilter  '*** File Name Filter control. ***
'        .Title = "You're Dialog Box Title Here"
'        .ButtonName = "OK button caption"
        .AllowMultiSelect = bMulti 'Note: if not specified defaults to True!
        
        '.Show  Returns: -1 if Open button or 0 if Cancel button is pushed!
        If .Show = -1 Then
        
          ReDim zSelected(.SelectedItems.Count) 'Make array the proper size.
          
          For lCnt = 1 To .SelectedItems.Count 'Load the array with selections.
             zSelected(lCnt) = .SelectedItems.Item(lCnt)
          Next lCnt
        
        End If
        
        GetFileToOpen = .SelectedItems.Count
        
    End With   'fd
    
End Function 'GetFileToOpen


Sub Test()

   Dim wkbSource As Workbook
   Dim zSourceFile() As String
   Dim lFileCnt      As Long
   
   
   ReDim zSelected(1)
   
   lFileCnt = GetFileToOpen(zSourceFile, "*.xlsx, *.xls, *.xlsm, *.xlsb", False)
   
   If lFileCnt > 0 Then
   
     MsgBox "You selected: " & zSourceFile(1), _
            vbOKOnly + vbInformation, "Selected File"
          
     Set wkbSource = Workbooks.Open(zSourceFile(1))
   
     MsgBox wkbSource.Name & " opened successfully!", _
            vbOKOnly + vbInformation, "File Opened"
   
   End If
   
    '*** Cleanup when done ***
   If Not wkbSource Is Nothing Then
     Application.DisplayAlerts = False
     wkbSource.Close False
     Application.DisplayAlerts = True
     Set wkbSource = Nothing  '*** Cleanup when done ***
   End If
   
End Sub

BTW: HTH = Hope this helps!

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote