Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for opening new files and copying from them - please help!
Is it not possible to do this? -- 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Opening Files - Macro | Excel Worksheet Functions | |||
Opening Files Using a Macro | Excel Discussion (Misc queries) | |||
Macro for opening files that changes names | Excel Discussion (Misc queries) | |||
Opening Multiple files and Copying the info all to one other sheet | Excel Discussion (Misc queries) | |||
Opening CSV files with VBA/macro | Excel Programming |