Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with macro to query data from DB
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Web query: Want to pause macro while Web query completes refreshin | Excel Programming | |||
external data automatic refresh through macro/query | Excel Discussion (Misc queries) | |||
another macro query - deleting a worksheet within a query | Excel Discussion (Misc queries) | |||
How can I Run a query from VB macro with out affect current data in the same sheet? | Excel Discussion (Misc queries) | |||
Macro to query data from a succession of websites and display in seperate sheets | Excel Programming |