Macro for opening new files and copying from them - please help!
 

Hi,

I'm designing a macro that, among other things, has pop-up windows that
allow the user to enter data.

I'd like the user to be able to input a number and, based on what they
put in, open a file and copy and paste from it.

E.g something like


Code:
--------------------

Get number from user
If number = 1
Open file1.xls
If number = 2
Open file2.xls

Copy column F from file that was opened
Paste into column K of original file

--------------------


I'm pretty new at VB macros, and also don't know if I need to be
opening a new workbook, sheet or what.
Any help really appreciated!
Thanks.

Mary


--
Mary T
------------------------------------------------------------------------
Mary T's Profile: http://www.excelforum.com/member.php...o&userid=30151
View this thread: http://www.excelforum.com/showthread...hreadid=498337

Macro for opening new files and copying from them - please help!
 

Is it not possible to do this? :confused:


--
Mary T
------------------------------------------------------------------------
Mary T's Profile: http://www.excelforum.com/member.php...o&userid=30151
View this thread: http://www.excelforum.com/showthread...hreadid=498337

Macro for opening new files and copying from them - please help!
 

Instead of asking the user for a number and worrying about if they typed
something valid, you could just let them point at the file to open.

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim myFileName As Variant
Dim curWks As Worksheet
Dim RngToCopy As Range
Dim DestCell As Range

Set curWks = ActiveSheet

myFileName = Application.GetOpenFilename(Filefilter:="Excel Files, *.xls")

If myFileName = False Then
Exit Sub 'user hit cancel
End If

Set wks = Workbooks.Open(Filename:=myFileName).Worksheets(1)

With wks
Set RngToCopy = .Range("K1", .Cells(.Rows.Count, "K").End(xlUp))
End With

With curWks
Set DestCell = .Cells(.Rows.Count, "K").End(xlUp).Offset(1, 0)
End With

RngToCopy.Copy _
Destination:=DestCell

wks.Parent.Close savechanges:=False
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Mary T wrote:

Hi,

I'm designing a macro that, among other things, has pop-up windows that
allow the user to enter data.

I'd like the user to be able to input a number and, based on what they
put in, open a file and copy and paste from it.

E.g something like

Code:
--------------------

Get number from user
If number = 1
Open file1.xls
If number = 2
Open file2.xls

Copy column F from file that was opened
Paste into column K of original file

--------------------

I'm pretty new at VB macros, and also don't know if I need to be
opening a new workbook, sheet or what.
Any help really appreciated!
Thanks.

Mary

--
Mary T
------------------------------------------------------------------------
Mary T's Profile: http://www.excelforum.com/member.php...o&userid=30151
View this thread: http://www.excelforum.com/showthread...hreadid=498337

--

Dave Peterson

Macro for opening new files and copying from them - please help!
 

Ok - First, that was a great reply and thank a lot. I'll be able to use
9/10ths of that. :)

Second: I do, however, -want- the file to be chosen as part of a user
entry, because that entry is part of a separate thing, and the file
that is opened depends on that entry (I simplified it slightly in my
original post).

I can now see that I merely have to use an if-then and then say
'Filename = x' in the code above. That's simple enough.

Question: How can I specify the folder one level below the current
folder?

The user is working in .../lists/data/myfile.xls
And the file to open is in .../lists/list1.xls

My problem is that I don't always know what the '...' part of the
directory will be (and the rest of the structure is fixed and I can do
nothing about it).

Any advice?

Thanks a lot!

-Mary


--
Mary T
------------------------------------------------------------------------
Mary T's Profile: http://www.excelforum.com/member.php...o&userid=30151
View this thread: http://www.excelforum.com/showthread...hreadid=498337

Macro for opening new files and copying from them - please help!
 

curdir returns the current folder.

You could use:
myPath = curdir & "\data\"

But it sounds like you really want one level up.

mypath = curdir & "\.."

might be what you want to use.

But be careful. You may not want the curdir.

You may want:
activeworkbook.path
or
ThisWorkbook.path





Mary T wrote:

Ok - First, that was a great reply and thank a lot. I'll be able to use
9/10ths of that. :)

Second: I do, however, -want- the file to be chosen as part of a user
entry, because that entry is part of a separate thing, and the file
that is opened depends on that entry (I simplified it slightly in my
original post).

I can now see that I merely have to use an if-then and then say
'Filename = x' in the code above. That's simple enough.

Question: How can I specify the folder one level below the current
folder?

The user is working in .../lists/data/myfile.xls
And the file to open is in .../lists/list1.xls

My problem is that I don't always know what the '...' part of the
directory will be (and the rest of the structure is fixed and I can do
nothing about it).

Any advice?

Thanks a lot!

-Mary

--
Mary T
------------------------------------------------------------------------
Mary T's Profile: http://www.excelforum.com/member.php...o&userid=30151
View this thread: http://www.excelforum.com/showthread...hreadid=498337

--

Dave Peterson

Macro for opening new files and copying from them - please help!
 

Yess!! It works.

It took a while to debug it, but I finally realized I needed to use
colons instead of slashes for the file structure. Is this a Mac thing
or because I'm in a Server folder? Anyway, no matter.

Thank you!
:)

Mary


--
Mary T
------------------------------------------------------------------------
Mary T's Profile: http://www.excelforum.com/member.php...o&userid=30151
View this thread: http://www.excelforum.com/showthread...hreadid=498337

Macro for opening new files and copying from them - please help!
 

I think it's a Mac thing (or maybe Unix--or the Mac version of Unix???).

But windows likes backslashes.



Mary T wrote:

Yess!! It works.

It took a while to debug it, but I finally realized I needed to use
colons instead of slashes for the file structure. Is this a Mac thing
or because I'm in a Server folder? Anyway, no matter.

Thank you!
:)

Mary

--
Mary T
------------------------------------------------------------------------
Mary T's Profile: http://www.excelforum.com/member.php...o&userid=30151
View this thread: http://www.excelforum.com/showthread...hreadid=498337

--

Dave Peterson