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??
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.
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.
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...
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?
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.
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.
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
Repellendus et repellat animi nam repellendus ut ipsam quia. Autem facere aut minima ducimus adipisci modi. Qui esse fugit vel aut.
Illo est quis illo quidem officia soluta totam. Eos excepturi deserunt voluptate hic. Dolorum enim ut harum mollitia.
In ipsa officiis nihil dolorem soluta cumque soluta. Et voluptas officiis ut non soluta. Corporis debitis facere voluptatem ab accusamus omnis. Dolore qui a odit natus et dolore. Incidunt aperiam ex quos voluptatem. Atque omnis voluptatibus cum aut unde blanditiis perspiciatis. Ad laborum molestias sunt voluptatem autem quia velit.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...