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

[Mary T]

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]

Is it not possible to do this?

 

[Dave Peterson]

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]

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

 

[Dave Peterson]

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]

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

 

[Dave Peterson]

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

But windows likes backslashes.