Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
File name in macro
I recorded a macro using a file name but would like to be able to use the
macro with any workbook. What file name would I put in the macro so that the macro looks for a generic file name and not a specific file name. file name example in the macro: Range("AK3").Select Windows("OrderStatus9-29-08.xls").Activate |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
File name in macro
Lizz45ie,
Depending on what you are trying to do with your macro you may not have to reference the workbook at all. If that is not the case (ie you are pulling data from another workbook that is open in the background, vlookup, match, etc), I would name the 'other' WB as whatever so you can then call it as needed to your activeworkbook (or worksheet). Try something like this: Sub SelectOtherWB() Dim MyWBName As String MyWBName = "TempFile.xls" ActiveWorkbook.SaveAs "C:\" & MyWBName, FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False ' ********* Your code here .. two samples given below .. use as needed ' Sample for having to reference another WB that is not your 'active' WB which is open at ' the same time VlookupMyData = Application.WorksheetFunction.VLookup _ (ActiveCell.Offset(0, -2), Workbooks("TempFile.xls").Sheets("MyActiveSheet"). Range("A:D"), 4, 0) ' Sample of having a New WB / WS open and you wanting to select the 'other' WB to have it active Windows("TempFile.xls").Activate End Sub If your recorded macro is performing a bunch of calculations, formatting, etc; then all you need to do is import the code into each workbook that you need the VB to run on. Hope this helps, J "Lizz45ie" wrote: I recorded a macro using a file name but would like to be able to use the macro with any workbook. What file name would I put in the macro so that the macro looks for a generic file name and not a specific file name. file name example in the macro: Range("AK3").Select Windows("OrderStatus9-29-08.xls").Activate |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
File name in macro
You can use the name of the ActiveWorkbook. The ActiveWorkbook is the
workbook that is currently visible and has focus in the Excel window. E.g., Windows(ActiveWorkbook.Name).Activate You can also use ThisWorkbook. ThisWorkbook always refers to the workbook containing the presently running code, regardless of what workbook might be active in Excel. E.g., Windows(ThisWorkbook.Name).Activate You can also use the ordinal number of the workbook. E.g, Windows(Workbooks(1).Name).Activate I suppose you could prompt the user for a workbook number, with code like the following: Sub AAA() Dim N As Long Dim S As String Dim WB As Workbook S = "Select the number corresponding to the desired workbook." & vbCrLf For N = 1 To Workbooks.Count S = S & CStr(N) & " " & Workbooks(N).Name & vbCrLf Next N N = Application.InputBox(prompt:=S, Title:="Select A Workbook", Type:=1) If N = 1 And N < Workbooks.Count Then Set WB = Workbooks(N) Windows(WB.Name).Activate Else MsgBox "Invalid Selection Number" End If End Sub Beyond that, there isn't really a "generic" workbook. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Tue, 4 Nov 2008 15:18:01 -0800, Lizz45ie wrote: I recorded a macro using a file name but would like to be able to use the macro with any workbook. What file name would I put in the macro so that the macro looks for a generic file name and not a specific file name. file name example in the macro: Range("AK3").Select Windows("OrderStatus9-29-08.xls").Activate |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
File name in macro
I failed to include in my original message that I'm trying to combine several
worksheets into one workbook and would like to use the macro to combine them by referencing the file(s) as a generic name. I tried the ActiveWorkbook but it doesn't work because the macro needs to access another workbook to move/copy the other worksheet into my final workbook. "Chip Pearson" wrote: You can use the name of the ActiveWorkbook. The ActiveWorkbook is the workbook that is currently visible and has focus in the Excel window. E.g., Windows(ActiveWorkbook.Name).Activate You can also use ThisWorkbook. ThisWorkbook always refers to the workbook containing the presently running code, regardless of what workbook might be active in Excel. E.g., Windows(ThisWorkbook.Name).Activate You can also use the ordinal number of the workbook. E.g, Windows(Workbooks(1).Name).Activate I suppose you could prompt the user for a workbook number, with code like the following: Sub AAA() Dim N As Long Dim S As String Dim WB As Workbook S = "Select the number corresponding to the desired workbook." & vbCrLf For N = 1 To Workbooks.Count S = S & CStr(N) & " " & Workbooks(N).Name & vbCrLf Next N N = Application.InputBox(prompt:=S, Title:="Select A Workbook", Type:=1) If N = 1 And N < Workbooks.Count Then Set WB = Workbooks(N) Windows(WB.Name).Activate Else MsgBox "Invalid Selection Number" End If End Sub Beyond that, there isn't really a "generic" workbook. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Tue, 4 Nov 2008 15:18:01 -0800, Lizz45ie wrote: I recorded a macro using a file name but would like to be able to use the macro with any workbook. What file name would I put in the macro so that the macro looks for a generic file name and not a specific file name. file name example in the macro: Range("AK3").Select Windows("OrderStatus9-29-08.xls").Activate |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2007 Macro to Open File, Delete Contents, Save New File | Excel Discussion (Misc queries) | |||
Macro to call a file that has a auto open macro in the file itself | Excel Programming | |||
Macro to insert values from a file and save another sheet as a .txt file | Excel Programming | |||
Automate open file, update links, run macro, close and save file | Excel Programming |