Excel Macros (Workbooks)

  • Thread starter Thread starter Anthony
  • Start date Start date
A

Anthony

I'm creating a macro where in my original workbook I put a
formula where it uses another file in another drive. So I
open the second workbook first, but now I need to get back
to my original workbook to put my formula in. I don't want
to use the name of that original workbook because the name
of the original workbook starts with 200411 (year and
month and this changes every month) and I want the macro
to work irregardless of the name of the original workbook.
Please help.
 
One way:

Dim wbOriginal As Workbook
Set wbOriginal = ThisWorkbook
Workbooks.Open <your path and filename>
wbOriginal.Activate


<peeve> "Irrespective" is a perfectly good word. So is "regardless". On
the other hand, "irregardless" is an abomination.</peeve>
 
Thanks for the help. (Macro and word usage)
-----Original Message-----
One way:

Dim wbOriginal As Workbook
Set wbOriginal = ThisWorkbook
Workbooks.Open <your path and filename>
wbOriginal.Activate


<peeve> "Irrespective" is a perfectly good word. So is "regardless". On
the other hand, "irregardless" is an abomination.</peeve>


.
Click to expand...
 
hahaha use Access for Macros; you can actually get stuff done (instead of
worrying about versioning and spreadsheet hell)
 
Anthony

You can use the GetOpenFileName method of the Application object to get the
name of the file you want to use. It will get the file name only (Not open
it) so you can then replace the name of the file in your macro. e.g.

Sub OpenFile()
Dim sFName As String
Dim wb As Workbook
'Next line simply gets name and path of workbook
sFName = Application.GetOpenFilename()
'This line opens it and assigns it to an object
Set wb = Workbooks.Open(sFName)
'Do what you want with workbook here
End Sub

PS: Excuse your welcome to the group, it is not normal behaviour around
here.


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
Back
Top