File name in macro

[Lizz45ie]

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

 

[J Smith 555]

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"), 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

 

[Chip Pearson]

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)

 

[Lizz45ie]

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.