Macro command to open a file

[sowetoddid]

I would like to have a cell in Book1 that I can input a file nam
into...Cell A1.

When the specific file name (C:\Book2.xls) is placed in A1, the macr
should be activated by a command button, open 'Book2', copy cell
A1:Z100, and paste them into Book 1 starting at cell B1.


That's a mouth full.

Originally, I wanted to record a macro that clicks the open file butto
and lets me select the file to open, and then have the macro continu
with the rest of its recorded copy and paste. But, the mix betwee
macro automation and user input does not seem viable.

Thank you

 

[sowetoddid]

I would like to be able to specify a file name because it will always b
a different file being copied.

An excel file named "03-04", which changes with the month and year wil
be generated each month by a 3rd party program. I just need to cop
that data into a new workbook that contains formulas in column A

 

[sowetoddid]

What about avoiding the Visual Basic approach to opening the file an
instead...

Assign each cell in Book1 a specific equation that pulls the data ou
of Book2. So, B1= Book2(A1)....B2=Book2(A2)


The only issue is how to change the name of Book2 to ensure that th
data is pulled from the correct file.

Anyone know how to make a macro so I can store the root file path i
the macro and simply type the file name ("Book2") into cell C1 an
click a Command Button that changes the equations in Book1. Th
equations would be changed to reference the appropriate file

 

[sowetoddid]

I have been reading pages on the internet to figure this out.

What about having a message box that opens with Workbook1 and asks th
user what the name of "Workbook2" will be. When the user inputs th
name and clicks "OK", the macro will update the source in each equatio
of Book1-Sheet1.

I have found this code, but how could I manipulate it to do what
want?

Sub Auto_Open()
YesNo = MsgBox
Select Case YesNo
Case vbYes
'Insert your code here if Yes is clicked
Case vbNo
'Insert your code here if No is clicked
End Selec

 

[sowetoddid]

This is another reference that I have been trying to work with...

http://www.j-walk.com/ss/excel/tips/tip82.ht

 

[sowetoddid]

I understand the difficulty in replacing the referenced file that i
embedded in an equation. This code would replace text, but the proble
is that the exact file name in the equation would be ever-changing.

Sub ChgInfo()
Dim Sht As Worksheet
For Each Sht In Worksheets
Sht.Cells.Replace What:="old stuff", _
Replacement:="new stuff", LookAt:=xlPart, MatchCase:=False
Next
End Sub

replacing the beginning contents that are in each equation shoul
work.....for example

cell B1 = 'C:\Documents and Settings\jdoe\M
Documents\[Book2.xls]Sheet1'!A1

would be replaced with

cell B1 = 'C:\Documents and Settings\jdoe\M
Documents\[Book3.xls]Sheet1'!A1

Instead of finding and replacing the "Book2" portion, it may be easie
to designate replacing the first 60 characters of the initial equatio
with the first 60 characters of the second equation. All of the exce
file names (ex. Book2, Book3, etc.) I am dealing with will have a
identical character length of 5 characters. So, the macro could alway
find and replace the first "60" characters.

Just thinking out loud...still looking for help.


Thanks

 

[Peo Sjoblom]

Shouldn't you really post this in programming?