ExcelBanter - help with macro to query data from DB

The best way to perform your request is to do it with a Learn Macro. follow
these steps and then make the modification that have listed

1) Worksheet Menu - Tools - Macro - Record New Macro
2) Worksheet Menu - Data - Ipmport External Data - New Database Query
MS Acess Database - OK
3) Select directory of database
4) seclect Database name - OK
5) Open the + (plus sign) and add STULINK, PerNum, Ln, Fn to columns in your
query.
6) Press Next until import data pop up window appears. Select cell B1.
7) worksheet Menu - Tools - Macro - Stop Recording
8) Modify macro like I did below.
a) Add Line Filename = Range("A1")
b) Put myfilename in place of actual filename. Make sure you add the two
double quotes (") after the = before the semicolon(;). also added to &.
This is waht I added in place of the filename

" & MyFilename & "

I also added a _ to continue the statement on another line.

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 10/22/2007 by Joel
'
MyFilename = Range("A1")
'
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=MS Access Database;DBQ=" & MyFilename & _
";DefaultDir=C:\TEMP;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout"), _
Array("=5;")), Destination:=Range("B1"))
.CommandText = Array( _
"SELECT `STULINK`, `PermNum`, `Ln`, 'Fn'")
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

"timmulla" wrote:

Hello, I wanted to see if somebody could me create a macro that could
retrieve data from a .mdx file and place the data into an excel worksheet.

I would like the user to type the following information into an worksheet to
define the DB file path and field names to import.

cell A1
user types the name of .mdx file to import data from.

cell A2
STULINK - field name

cell A3
PermNum - field name

cell A4
Ln - field name

cell A5
Fn - field name

Any help would be appreciated. Thanks,
--
Regards,

timmulla