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?

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?

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?
.