Excel Macro not working after change in file name!!!

jmdude's picture
Rank: Orangutan | banana points 290

I recently created a system of macros. Is there a way to apply the already-created macro to different excel files without the original file open?? I tried taking the original excel file which i used to create the macros and "saved as" a different file name and sure enough the macros didnt work (as I received a Debug error, alerting me the original file could not be found) but when I have the original file open along with the newly saved-as file the macro works...

Also, I checked to see if there were any references in my code to the workbook that contains the code. There was, for example:

Application.Run "'WorkbookXYZ.xls'!Macro24"
ActiveCell.Offset(0, -6).Range("A1").Select

Any suggestions??

Investment Banking Interview Course

  • 7,548 questions across 469 investment banks. Crowdsourced from over 500,000 members.
  • Technical, behavioral, networking, case videos, templates. All included.
  • Most comprehensive IB interview course in the world.

Comments (8)

Jul 11, 2008

As far as I know, that would be difficult to do, short of creating a macro that would open that master workbook, run your specific macro, then close the master workbook. That almost seems not worth the effort for the additional problems it would cause with loading, etc. The best method would just to copy the vba code for the macro you want into the new worksheet. In the code, you'd then want to remove the portion of the calling the macro that refers specifically to the previous workbook.

I'm not an expert in VBA though, so maybe its possible. But just copying the code, or keeping the other workbook open would be the only way I know of.

Jul 11, 2008

hide WorkbookXYZ.xls by going to Windows|Hide. Then i believe you need to save this file in C:\Windows\Program Files\Microsoft Office\Excel\XLSTART" or in other words, the "XLSTART" directory where Excel is located.

Now, whenever you start Excel, WorkbookXYZ.xls will contain the macro and be hidden in the background so you dont see it. You can then run the macro on any workbook you need.

Besides that, there is no way that i know of you being able to run a macro without its workbook being open in some way. You may be able to save the macro as an executable and have that hidden in the background, but im pretty sure the method above accomplishes the same thing.

Hope that helps.

Jul 11, 2008

Additionally, you should put a line of code in the beginning of the macro that says something like

Dim ThisWorkbook as String
Dim path as String
ThisWorkbook = ActiveWorkbook.Name
path = ActiveWorkbook.Path

and then later in the code:

Application.Run path & "\'" & ThisWorkbook & ".xls'!Macro24"
ActiveCell.Offset(0, -6).Range("A1").Select

Does that make sense? this would work even if the two workbooks are in different directories, but if they are in the same directory then you can just take out the "path" variable all together. I think that solves what youre asking...

Jul 11, 2008

I just don't follow why if you're going to point it out to this worksheet anyway you don't just simply use:

Application.Run "Macro24", so it just runs the macro in the current book. Am I missing something?

Jul 11, 2008

he is working in other books, doing "Run Macro24" will only run the macro in the activeworkbook... i believe his macro jumps around workbooks, so the active one may not have the desired macro.

Learn More

7,548 questions across 469 investment banks. The WSO Investment Banking Interview Prep Course has everything you'll ever need to start your career on Wall Street. Technical, Behavioral and Networking Courses + 2 Bonus Modules. Learn more.

Jul 11, 2008

Alright I see what you're saying, I was just a little unclear about what you meant. I still think its probably simplest to just copy the code haha.

Jul 11, 2008

Here's one I have that does just that without visibly opening the Excel file:

Note: in order for this to work, in the VBA window goto Tools-->References. Then find one that says something to the effect of "Microsoft Excel 10.0 Object Library" and select it, then click OK.

Sub FormatExcelFile(filename As String, NumberRecords As Integer)
Dim xl As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Set xl = New Excel.Application
Set wbk = xl.Workbooks.Open(filename)
Set wks = wbk.Sheets(1)
wks.Activate
With wks
.Range("H2:H" & NumberRecords + 1).NumberFormat = "#,##0"
For i = 3 To (NumberRecords + 1) Step 2
If i / 2 = Int(i / 2) Then
.Range("A" & i & ":M" & i).Interior.ColorIndex = 2
Else
.Range("A" & i & ":M" & i).Interior.ColorIndex = 36
End If
Next i
With .Range("A2:M" & NumberRecords + 1, "A1:J1").Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Range("A2:M" & NumberRecords + 1, "A1:J1").Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Range("A2:M" & NumberRecords + 1, "A1:J1").Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Range("A2:M" & NumberRecords + 1).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Range("A2:M" & NumberRecords + 1, "A1:J1").Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Range("A2:M" & NumberRecords + 1).Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = xlAutomatic
End With
With .Range("A1:M1").Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
.Range("A1:M1").Font.Bold = True
.Range("A1:M1").Interior.ColorIndex = 15
.Range("A1:M1").HorizontalAlignment = xlCenter
.Columns("A:M").EntireColumn.AutoFit
.Range("A2").Select
xl.ActiveWindow.FreezePanes = True
.Range("A1").AutoFilter
End With
wbk.Save
wbk.Close
xl.Quit
Set wks = Nothing
Set wbk = Nothing
Set xl = Nothing
End Sub

Jul 11, 2008