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

--
Message posted from http://www.ExcelForum.com

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

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

--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com

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

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


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

--
Message posted from http://www.ExcelForum.com

Set oWB = Activeworkbook
Workbooks(Range("A1").Open
Worksheets(1).Range("A1:Z100").Copy
Destination:=oWB.ACtivesheet.Range("B1")
Activeworkbook.Close
oWB.ACtivate

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"sowetoddid " wrote in message
...
I would like to have a cell in Book1 that I can input a file name
into...Cell A1.

When the specific file name (C:\Book2.xls) is placed in A1, the macro
should be activated by a command button, open 'Book2', copy cells
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 button
and lets me select the file to open, and then have the macro continue
with the rest of its recorded copy and paste. But, the mix between
macro automation and user input does not seem viable.

Thank you.


---
Message posted from http://www.ExcelForum.com/

I wouldn't really put the name of the file in a cell -- raises the risk
of a typo...

Use something like:

Option Explicit

Sub testIt()
Dim ThisWB As Workbook, OpenedWB As Workbook, _
OpenFileName As Variant
Set ThisWB = ThisWorkbook
OpenFileName = Application.GetOpenFilename()
If LCase(TypeName(OpenFileName)) = "boolean" Then
Else
Set OpenedWB = Workbooks.Open(OpenFileName)
OpenedWB.Sheets(1).Range("A1:Z100").Copy
ThisWB.Worksheets("sheet1").Range("b1").PasteSpeci al _
xlPasteValuesAndNumberFormats
OpenedWB.Close False
End If
End Sub

Note that you did not specify which worksheet within the respective
workbooks were the source/destination. I made some assumptions. <g

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , sowetoddid
says...
I would like to have a cell in Book1 that I can input a file name
into...Cell A1.

When the specific file name (C:\Book2.xls) is placed in A1, the macro
should be activated by a command button, open 'Book2', copy cells
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 button
and lets me select the file to open, and then have the macro continue
with the rest of its recorded copy and paste. But, the mix between
macro automation and user input does not seem viable.

Thank you.


---
Message posted from http://www.ExcelForum.com/

To understand what this code is doing, I am testing it out...this wa
pasted into a VB module...

Sub testIt()
Dim ThisWB As Workbook, OpenedWB As Workbook, _
OpenFileName As Variant
Set ThisWB = ThisWorkbook
OpenFileName = Application.GetOpenFilename()
If LCase(TypeName(OpenFileName)) = "boolean" Then
Else
Set OpenedWB = Workbooks.Open(OpenFileName)
OpenedWB.Sheets(1).Range("A1:Z100").Copy
ThisWB.Worksheets("sheet1").Range("b1").PasteSpeci al _
xlPasteValuesAndNumberFormats
OpenedWB.Close False
End If
End Sub

...nothing happened


I am using the following naming conventions...

Workbook 1 = "Master File"
Sheet 1 = "Music"

Workbook 2 = "121 3-4" (this will change to "060 3-4", "040 3-5", etc
But is will always maintain the same format)

Sheet 1 = "Output

--
Message posted from http://www.ExcelForum.com

I am a beginner at using VB. The parts I don't quite understand are...

1. How is this code determining the name of the Workbook I want t
copy data from?

2. How is this code finding the workbook? Doesn't a path need to b
specified?



Thanks

--
Message posted from http://www.ExcelForum.com

Trying to learn this,
I should input my specific names as...

Sub testIt()
Dim ThisWB As Workbook, OpenedWB As Workbook, _
OpenFileName As Variant
Set ThisWB = ThisWorkbook
OpenFileName = Application.GetOpenFilename()
If LCase(TypeName(OpenFileName)) = "boolean" Then
Else
Set OpenedWB = Workbooks.Open(OpenFileName)
OpenedWB.Sheets(1).Range("A1:Z100").Copy
ThisWB.Worksheets("music").Range("b1").PasteSpecia l _
xlPasteValuesAndNumberFormats
OpenedWB.Close False
End If
End Sub


And in the other example...

Set oWB = Activeworkbook
"Master File"((Range("A1")).Open
Music.Range("A1:Z100").Copy
Destination:=oWB.ACtivesheet.Range("B1")
Activeworkbook.Close
oWB.ACtivate


Please correct me. Also. Any suggestions for a good source o
learning this? I have seen some downloadable help a
Add-ins.com.....Any experience with that?

Many thanks

--
Message posted from http://www.ExcelForum.com

"sowetoddid " wrote in message
...
I am a beginner at using VB. The parts I don't quite understand are...

1. How is this code determining the name of the Workbook I want to
copy data from?


The GetOp[enFilename is used to select a file, and this is then set to the
variable OpenedWB when it is opened. It is data copied frfom OpenedWB so
that is how it knows.


2. How is this code finding the workbook? Doesn't a path need to be
specified?


No because it opens a file browser for you to go and get it. This returns
the path and filename.

Wow! Bob, that is incredible. I saw it start working, but it caught
problem toward the end.

This error came up....

"Runtime error '1004'"
"PasteSpecial method of range class failed"


What exactly does that mean

--
Message posted from http://www.ExcelForum.com

It is probably because the target worksheet has not been selected before
pastespecial.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"sowetoddid " wrote in message
...
Wow! Bob, that is incredible. I saw it start working, but it caught a
problem toward the end.

This error came up....

"Runtime error '1004'"
"PasteSpecial method of range class failed"


What exactly does that mean?


---
Message posted from http://www.ExcelForum.com/

Ok....ran it again and it gave me a "subscript out of range" error. I
also pasted the data starting at A1 instead of B1.

This is the macro that I ran...

Sub testIt()
Dim ThisWB As Workbook, OpenedWB As Workbook, _
OpenFileName As Variant
Set ThisWB = ThisWorkbook
OpenFileName = Application.GetOpenFilename()
If LCase(TypeName(OpenFileName)) = "boolean" Then
Else
Set OpenedWB = Workbooks.Open(OpenFileName)
OpenedWB.Sheets(1).Range("A1:Z100").Copy
ThisWB.Worksheets("sheet1").Range("b1").PasteSpeci al _
xlPasteValuesAndNumberFormats
OpenedWB.Close False
End If
End Su

--
Message posted from http://www.ExcelForum.com

I read on microsoft's page that this is fixed by using an "8" instead o
xlColumnWidths. What about for the PasteSpecial in this code bein
used

--
Message posted from http://www.ExcelForum.com

Where (what line) did you get the error?

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , sowetoddid
says...
Ok....ran it again and it gave me a "subscript out of range" error. It
also pasted the data starting at A1 instead of B1.

This is the macro that I ran...

Sub testIt()
Dim ThisWB As Workbook, OpenedWB As Workbook, _
OpenFileName As Variant
Set ThisWB = ThisWorkbook
OpenFileName = Application.GetOpenFilename()
If LCase(TypeName(OpenFileName)) = "boolean" Then
Else
Set OpenedWB = Workbooks.Open(OpenFileName)
OpenedWB.Sheets(1).Range("A1:Z100").Copy
ThisWB.Worksheets("sheet1").Range("b1").PasteSpeci al _
xlPasteValuesAndNumberFormats
OpenedWB.Close False
End If
End Sub


---
Message posted from http://www.ExcelForum.com/

In article , sowetoddid
says...

..nothing happened

What does that mean?

Also, please format any code for readability.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , sowetoddid
says...
To understand what this code is doing, I am testing it out...this was
pasted into a VB module...

Sub testIt()
Dim ThisWB As Workbook, OpenedWB As Workbook, _
OpenFileName As Variant
Set ThisWB = ThisWorkbook
OpenFileName = Application.GetOpenFilename()
If LCase(TypeName(OpenFileName)) = "boolean" Then
Else
Set OpenedWB = Workbooks.Open(OpenFileName)
OpenedWB.Sheets(1).Range("A1:Z100").Copy
ThisWB.Worksheets("sheet1").Range("b1").PasteSpeci al _
xlPasteValuesAndNumberFormats
OpenedWB.Close False
End If
End Sub

..nothing happened


I am using the following naming conventions...

Workbook 1 = "Master File"
Sheet 1 = "Music"

Workbook 2 = "121 3-4" (this will change to "060 3-4", "040 3-5", etc.
But is will always maintain the same format)

Sheet 1 = "Output"


---
Message posted from http://www.ExcelForum.com/

OK, you lost me. I don't use xlColumnWidths anywhere in my code!?

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , sowetoddid
says...
I read on microsoft's page that this is fixed by using an "8" instead of
xlColumnWidths. What about for the PasteSpecial in this code being
used?


---
Message posted from http://www.ExcelForum.com/

The error came at this part...

ThisWB.Worksheets("music").Range("b1").PasteSpecia l _
xlPasteValuesAndNumberFormats


If I remove "AndNumberFormats", then the code works. However, it doe
not carry over the format

--
Message posted from http://www.ExcelForum.com

Bob or someone else...


To make this a little more complicated, but very useful to me....Ho
would you take the filename of the OpenedWB and automatically do
SaveAs on ThisWB. What code would need to be inserted?


So, the original workbook (ThisWB) would be saved as "OpenedWB - F".
would like to rename the file with this name to preserve the origina
"ThisWB".

The "- F" at the end is just so I do not write over "OpenedWB".


Thank you, Thank you

--
Message posted from http://www.ExcelForum.com

I just found my old post on the question about saving the file a
"OpenedWB - F", with OpenedWB being the actual name of the other fil
in the macro.


Does this work...

ActiveWorkbook.SaveAs FileName:="OpenedWB - F"


How would I specify a directory?


Gracias

--
Message posted from http://www.ExcelForum.com

Hi
see your other post

--
Regards
Frank Kabel
Frankfurt, Germany


I just found my old post on the question about saving the file as
"OpenedWB - F", with OpenedWB being the actual name of the other file
in the macro.


Does this work...

ActiveWorkbook.SaveAs FileName:="OpenedWB - F"


How would I specify a directory?


Gracias!


---
Message posted from http://www.ExcelForum.com/