Macro command to open a file

S

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
 
S

sowetoddid

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
 
S

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
 
S

sowetoddid

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
 
B

Bob Phillips

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)
 
T

Tushar Mehta

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").PasteSpecial _
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
 
S

sowetoddid

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").PasteSpecial _
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
 
S

sowetoddid

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
 
S

sowetoddid

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").PasteSpecial _
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
 
B

Bob Phillips

sowetoddid > said:
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?
Click to expand...

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?
Click to expand...

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

sowetoddid

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
 
B

Bob Phillips

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)
 
S

sowetoddid

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").PasteSpecial _
xlPasteValuesAndNumberFormats
OpenedWB.Close False
End If
End Su
 
S

sowetoddid

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
 
T

Tushar Mehta

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
 
S

sowetoddid

The error came at this part...

ThisWB.Worksheets("music").Range("b1").PasteSpecial _
xlPasteValuesAndNumberFormats


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

sowetoddid

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
 
S

sowetoddid

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro command to open a file 6
Copying a Name's Contents 8
Macro needed 3
Make Macro Work On A Different PC 5
Macro that will insert a simple text string in top cell of text file 1
Opening a new workbook via a macro 2
macro to fill cells 1
Using replace in VBA 7

Top