Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro help
I'm new to the use of macro. I recorded a macro to get
external data, with defined ranges and fields from one of many files that contain the exact same format of data. Works great but I want to be able to select the file to get the data from in the macro. Can the macro be edited to do this? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro help
fname = Application.GetOpenFileName()
fname will either be False or contain the fully qualified path of the file the user selected using the file open dialog. It does not open the file. You can now use the information to open the file This is what you asked for. See help on the GetOpenfileName method for optional arguments and a further explanation. Where you had workbooks.open "C:\Myfiles\Myfile.xls" you would now have Dim fName as String fname = Application.GetOpenFileName() if not fname = "False" then workbooks.Open fName Else Exit sub end if as an example -- Regards, Tom Ogilvy "tim" wrote in message ... I'm new to the use of macro. I recorded a macro to get external data, with defined ranges and fields from one of many files that contain the exact same format of data. Works great but I want to be able to select the file to get the data from in the macro. Can the macro be edited to do this? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro help
Yes, it can be done. From Tools menu, Macros: On the
list of macros select the one you created and then press the Edit button. Will take you to the VBA editor. In the main code pane you will see the VBA code created when you recorded the macro. Somewhere in that code you should see a line somewhat like this: With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _ "ODBC;DSN=MS Access Database;DBQ=G:\Stats\HOSPITAL STATISTICS DATA.mdb;DriverId=25;FIL=MS" _ ), Array(" Access;MaxBufferSize=2048;PageTimeout=5;")), Destination:=Range( _ "A1")) This is the code that defines where the data in the query is coming from. If it is incomprehensible to you, don't worry too much - for what you want you only need to find and change the file path, which you can see in the above is G:\Stats\HOSPITAL STATISTICS DATA.mdb for my example. You can just type in another path, or you could let the user choose the file from a list (or even a file open dialog box) and use their choice as the path. To let the user specify, you would add a Dim statement at the top part of the macro (right after the Sub MacroName() statement) to set up a string variable to store the path: Dim UserPath as String Then at the beginning of the code, pop up an input box to let the user specify the path and store the input value into your UserPath variable UserPath = InputBox("Enter the file path:") Now you would substitute the UserPath into the statement where the file path goes, e.g: With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _ "ODBC;DSN=MS Access Database;DBQ=" & UserPath & ";DriverId=25;FIL=MS" _ ), Array(" Access;MaxBufferSize=2048;PageTimeout=5;")), Destination:=Range( _ "A1")) This is simplified, and as I am not sure of your programming experience or knowledge of VBA I don't know how much info you might need to proceed, but I wanted you to know that this is indeed possible - but you will need to dig into the VBA code. K Dales -----Original Message----- I'm new to the use of macro. I recorded a macro to get external data, with defined ranges and fields from one of many files that contain the exact same format of data. Works great but I want to be able to select the file to get the data from in the macro. Can the macro be edited to do this? . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) | |||
macro to delete entire rows when column A is blank ...a quick macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |