Querying external data thru macros

Hallu,

I'm trying to import some external data, and my query for it is that it
needs to be a specific date. I was wondering if i could write a macros
so, when i run it there will be a inputbox that pops up asking which
date i would like to query for before it imports the data.

Any ideas?Thanks,
bLySs


---
Message posted from http://www.ExcelForum.com/

0
7/16/2004 8:51:31 AM
excel 39879 articles. 2 followers. Follow

4 Replies
311 Views

Similar Articles

[PageSpeed] 50

bLySs (This in XL2003, don't know if this will effect anything)...

Maybe you can extract the relevant from the code below, which I set up this
morning.  It queries an access database on my hard drive called test.mdb,
takes two fields from a table called tblCatalogueHeader and sets up a
parameter of CatCode.  It then adds a parameter called CatCode and sets the
parameter to prompt the user xlPrompt with the text shown. (It can also be
set with a fixed string or call the value from another cell. (I have
deliberately left all the methods out of the querytable setup as it is for
this example unnecessary code, but if you want the query to background
refresh, overwrite cells, etc, these will need to be set). I actually
learned something myself with this today, thanks!

Sub SetParameters()
Dim qry As QueryTable
Dim prm As Parameter
    With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=MS Access
Database;DBQ=C:\Test.mdb;DefaultDir=C:;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;" _
        , Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT tblCatalogueHeader.CatCode, tblCatalogueHeader.AmountInv" &
Chr(13) & "" & Chr(10) & _
        "FROM `C:\Test`.tblCatalogueHeader tblCatalogueHeader" & Chr(13) &
"" & Chr(10) & _
        "WHERE (Catcode=?)")
        .Name = "test"
    End With
Set qry = ActiveSheet.QueryTables(1)
Set prm = qry.Parameters.Add("CatCode", xlParamTypeVarChar)
qry.Parameters(1).SetParam xlPrompt, "Enter a Catalogue Code (e.g 00000101)"
qry.Refresh
End Sub

-- 
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS


"bLySs >" <<bLySs.19hazt@excelforum-nospam.com> wrote in message
news:bLySs.19hazt@excelforum-nospam.com...
> Hallu,
>
> I'm trying to import some external data, and my query for it is that it
> needs to be a specific date. I was wondering if i could write a macros
> so, when i run it there will be a inputbox that pops up asking which
> date i would like to query for before it imports the data.
>
> Any ideas?Thanks,
> bLySs
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
7/17/2004 9:48:07 AM
Hi bLySs.............

I'm not much of a Coder, but if I understand your request correctly then the
following macro does for me what I think you want to do........that is,
bring up a pop-up window requesting some input......in my case a
"BOMNumber", (Bill of Materials number)......perhaps it's something you can
adapt from for your solution.........the rest of the code is a Query into
our MRP program called FourthShift........probably don't make much sense,
but here it is..........(watch out for the word-wrap)
----------------------------------------------------------------------------
---------------

 Sub GetBOM_FromFourthShift()

' DOES AUTOMATIC QUERY FOR any BOM entered into InputBox

Dim inputstr

    Const sFileName As String = "BOMQuery"

    Worksheets("BOMQuery").Activate
    inputstr = Range("BOMNumber").Value

    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
         "ODBC;DSN=fs1 - MSS-Fourth
Shift;ServerName=FS1.1583;ServerDSN=fs1 - MSS-Fourth
Shift;UID=clr;PWD=chuck;ArrayFetchOn=1;ArrayBufferSize=8" _
       ), Array(";DBQ=M:\Mfgsys\System;CODEPAGE=1252;")),
Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT ITEM_MULTILEVELBILL.END_ITEM, ITEM_MULTILEVELBILL.LEVEL,
ITEM_MULTILEVELBILL.PT_USE, ITEM_MULTILEVELBILL.SEQN,
ITEM_MULTILEVELBILL.IN_REV, ITEM_MULTILEVELBILL.COM_TYP,
ITEM_MULTILEVELBILL.COMPONEN" _
        , _
        "T, ITEM_MULTILEVELBILL.COMP_DESC, ITEM_MULTILEVELBILL.QUANTITY,
ITEM_MULTILEVELBILL.COMP_UM, ITEM_MULTILEVELBILL.QTY_TYP,
ITEM_MULTILEVELBILL.PARNT_DESC" & Chr(13) & "" & Chr(10) & "FROM
ITEM_MULTILEVELBILL ITEM_MULTILEVELBILL" & Chr(13) & "" & Chr(10) & "WHERE
(ITEM_MULTILEVELBILL.END_ITE" _
        , "M='" & inputstr & "')")

        .name = "Query from FS - MSS-Fourth Shift_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With

   Range("Parnt_Desc").Value = Range("L2").Text

End Sub
Vaya con Dios,
Chuck, CABGx3


"bLySs >" <<bLySs.19hazt@excelforum-nospam.com> wrote in message
news:bLySs.19hazt@excelforum-nospam.com...
> Hallu,
>
> I'm trying to import some external data, and my query for it is that it
> needs to be a specific date. I was wondering if i could write a macros
> so, when i run it there will be a inputbox that pops up asking which
> date i would like to query for before it imports the data.
>
> Any ideas?Thanks,
> bLySs
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
croberts (1377)
7/17/2004 5:07:17 PM
"CLR" wrote ...

> I'm not much of a Coder

I thought with that alias you were a hard-core dot net coder <g>.

Jamie.

--
0
jamiecollins (192)
7/19/2004 9:59:14 AM
lol............thanks anyway, but

CABGx3 = Coronary Artery Bypass Graft, times 3........... (a Triple Bypass
Survivor)

Vaya con Dios,
Chuck, CABGx3




"Jamie Collins" <jamiecollins@xsmail.com> wrote in message
news:2ed66b75.0407190159.ee34974@posting.google.com...
> "CLR" wrote ...
>
> > I'm not much of a Coder
>
> I thought with that alias you were a hard-core dot net coder <g>.
>
> Jamie.
>
> --


0
croberts (1377)
7/19/2004 10:33:47 PM
Reply:

Similar Artilces:

Formatting a subtotal line with a macro
I already have in use a macro that runs a sort and subtutal function. I would like to format the subtotal row using visual basic since there is already a macro in place. Hi Look at this: ActiveCell.Font.Bold = True Regards, Per "DJDKAL" <daniel.kalfayan@hotmail.com> skrev i meddelelsen news:A86040B0-B4C4-4E11-A80C-E80132C9710F@microsoft.com... >I already have in use a macro that runs a sort and subtutal function. I > would like to format the subtotal row using visual basic since there is > already a macro in place. This is what I've g...

Queries and Charts
Does anyone know why the expressions in queries work fine for reports but not charts? ...

Pass parameter from FORM to QUERY
I have an append query that I trying to call from a cmd btn...but it prompts me for the parameter(ie QuoteID)... How do I tye it into my call: Dim stQueName As String stQueName = "Quote Query" DoCmd.OpenQuery stQueName, acNormal, acEdit On Apr 13, 12:56 pm, jlt...@hotmail.com wrote: > I have an append query that I trying to call from a cmd btn...but it > prompts me for the parameter(ie QuoteID)... > How do I tye it into my call: > > Dim stQueName As String > stQueName = "Quote Query" > DoCmd.OpenQuery stQueName, acNormal, acEd...

VB Macro #2
I have a macro that is generating Excel files. When the macro runs, it saves two different tabs in the workbooks as values. It needs to test and wait until the formulas that are pulling amounts from another database are correctly populated with the amounts. Right now, I am getting N/A# instead of amounts for some, but not all, of the files. I think the problem is that the formulas have not completed pulling the amounts before the macro pastes that tab as zeros. (N/A# is what appears when the formulas have not completed calculating - once they have completed, the N/A# is replaced ...

Can the data in a chart table be right justified?
Ecxel 2003 and previous versions of the product center the data in the data columns. Can the data in the columns of a chart table be right justified? In article <DABF738B-6C0D-458B-B082-FA9BD8F126A7@microsoft.com>, =?Utf- 8?B?c2FtIGVhZ2xl?= <sam eagle@discussions.microsoft.com> says... > Ecxel 2003 and previous versions of the product center the data in the data > columns. Can the data in the columns of a chart table be right justified? > Have you tried to format the table? If yes, and you haven't been successful it is probably because XL allows very limited cust...

Data migration - Adventure Works
Hiya... I have a company where the adventure works db has been used and had a lot of data populated into the system. We have now purchased MSCRM and have obtained the company reg keys. What is the easiest way to get the data from the 1 system to the next? We will be establishing a new AD domain and users for the new system.... Data Migration Framework? Redeployment Framework? ;) redeploment tools http://www.microsoft.com/downloads/details.aspx?FamilyID=bfced393-61db-49af-9a50-4a90b311fa7d&DisplayLang=en -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "funboy...

Problem with macro assigned to custom toolbar button
Problem with macro assigned to custom toolbar button I am having a probelm with macros assigned to a custom button on custom button. I am using excel 2000. 1. I have created a macro called "Jump" in a workbook named "Test1" 2. I then created a custom toolbar and added a custom toolbar button t that toolbar. 3. Through the customize dialog box I assign the Macro "Jump" to th custom button. Everything works fine UNTIL. If I do a SAVE AS for the workbook "Test1" and call it something els like "Test2" and then close "Test2" an...

Changing query execution sequence
Hi all, I got a spreadsheet which would execute a bunch of queries. It's noted that the queries are executing in the sequence of when it was added to the spreadsheet. Does anyone out there know of a way to switch the order without deleting and recreating them? Thanks! Wing ...

Label a chart of counts with other percentage data
Here's the data: Group 2005 2006 2007 LTM Data A 19.4% 22.8% 21.2% 19.9% Profitability A 6 7 7 7 Count B 9.5% 31.6% 30.4% 30.7% Profitability B 2 3 3 3 Count C 22.4% 23.6% 16.6% 17.6% Profitability C 15 16 17 18 Count D 19.2% 20.5% 15.9% 13.7% Profitability D 8 8 9 10 Count I have successfully generated a stacked bar chart that shows the counts per group by year. Now I would like to include a label for each group to show profitability for each group in each year in the 4 stacks. How would I do that? Thanks, --...

Creating a chart based on the data in an embedded worksheet
Hi, I have a worksheet with several embedded worksheets. I would like to create a chart based on the data of one of the embedded worksheets without putting the chart in the embedded worksheet. I have tried unsuccessfully to do this. I just wondered if anyone knew how to do it. Thanks, JK JK - You're embedding worksheets within worksheets? Why? Why not just insert the worksheets in line with the main worksheet? To open or edit the embedded worksheet, the parent Excel has to open another instance of Excel, and the chart on the outside of this other instance will never be able to acce...

Macros not performing correctly
I recorded a macro to place a vlookup formula in four cells that are side by side. When I run the macro it only performs the first task, it does not go on to do the other three. I used the tab key to go to the next cell while recording the macro, I don't know if that's why it's not working or if it's something else. The script looks like this: Sub EERates() ' ' EERates Macro ' Macro recorded 3/4/2005 by BCBSOK ' ' ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Sheet1!R3C1:R102C2,2,0)" Range("F15").Select ActiveCell.FormulaR1C...

multiple iterations of same query
Running Access 2003 on Vista. I have a query that chooses 15 records at random. This works fine. What I want to do is run the same query for approx 110 different users, each with their own randomly selected 15 records. Obviously, I want to avoid manually running the same query that many times. How can I get around this? Post your query SQL. -- Build a little, test a little. "Nathan" wrote: > Running Access 2003 on Vista. > > I have a query that chooses 15 records at random. This works fine. > > What I want to do is run the same query fo...

Data validation list from another worksheet?
Is it possible that the value list for data validation be populated fro another worksheet? Puneet Aror -- puneetarora_1 ----------------------------------------------------------------------- puneetarora_12's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1840 View this thread: http://www.excelforum.com/showthread.php?threadid=38572 Sure is! Use a named range as described here: http://www.officearticles.com/excel/drop-down_using_data_validation_in_microsoft_excel.htm ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "punee...

"external data sources" and "external data ranges"
what is the difference between these? i have run the vb macro code on http://support.microsoft.com/kb/330383 to check if i have any external data sources or data ranges but there are none. the reason i need to know is becuase i'm working with office sharepoint server 2007 and i cannot access a file thru the web access web part because it says: "The workbook that you selected cannot be loaded because it contains the following features that are not supported by Excel Services: External data ranges (also called query tables)" anyone? thanks ...

How to show query parameters on an Excel page header or worksheet?
Is it possible to display query parameters on a page header or on a worksheet? I have a worksheet that uses a query to retrive data from an ODBC database. The Query prompts for the Start Date and End Date. I would like to be able to print the worksheet and display the Start Date and End Date the user typed in. Thank you for your assistance. ...

Tricky ComboBox / Filter query
Here's one On Sheet1, from A1:A2931 I have dates, every day from 01.01.02 to 31.12.2009 (A1="01/01/2002", A2="02/01/2002", etc). On a Userform I have 2 ComboBoxes: 1 for month, one for year. Is it possible to use these to filter Sheet1 and leave only those dates chosen in the dropdowns (eg, December 2004 only)? TIA Alan ...

Query question 12-11-07
I have a question that I hope you all can help me with. When I run a query, the results come back with multiple lines of data for an order because of multiple critereas in another field. Here is an example of the data returned to my query: Order Status A In Process A Sent B In Process C In Process I would like to see only the data for orders that have not been shipped, and totally exclude data for orders that have been sent. In the example above, I would like my query results to show Orders B and C, but no data for A since it has al...

How can I count unique values in a query in the report footer 12-16-07
I have a report that gives me the count of the status of individuals . This works fine as long is there is only one record in the query (in my query there is one record per month). When I query 12 months (individuals may appear in various months) it counts each record of an individual. For Example in a query considering 12 months for a widow Jones it may count her 12 times and for a survivor named Smith may count her 8 times: Widows 12 Survivors 8 I would like to add a count in the report footer that will tell me how many unique individuals I have in the report (Example widows: Tot...

Source data, in Chart Menu, is grey and not accessible.
I set up a pivot table and designed a chart, saved and closed out of the workbook. Upon returning to work on this project, when selecting the chart menu to work with my source data, the source data is grey and not accessible. What might be causing this and can I correct it? It's a pivot chart. As soon as it's created, it's linked permanently to the pivot table, and you cannot change the source data. you cannot add more data that's not in the pivot table to the chart, nor can you remove data that is in the pivot table from the chart. - Jon ------- Jon Peltier, Microsoft...

Query Active Directory for OWA users
Can anyone tell me if it possible to query active directory to resolve all users with access to outlook web access enabled? and if so, how? this is making me crazy. By default all mailbox enable users have access to outlook web access. You can query the AD for the attribute protocolSettings to find user that have OWA disable. Good article on OWA in the link below. http://www.msexchange.org/tutorials/Securing-Exchange-Server-2003-Outlook-Web-Access-Chapter5.html <rob@robnles.com> wrote in message news:1110916531.628512.237870@g14g2000cwa.googlegroups.com... > Can anyone tell me...

how to use this to query my table?
I have a table with 30,000 records. I'd like to use this http://www.regular-expressions.info/email.html to query my email fields (show invalid emails). I'd like to use the first expression listed on that site. How do I proceed? On Sun, 16 May 2010 18:52:30 -0700 (PDT), Song <song.usa@gmail.com> wrote: Write a public function in a standard module: Public Function TestEmail(myEmailField as string) as Boolean Dim pattern As String pattern = "\b[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}\b" Dim r As RegExp 'Requires a reference to Microsoft VBScr...

Query-based DG's
Hi there, I need to create some DG's for departmental staff, which I have no problem in doing. However, i'm getting stuck when someone belongs to more than 1 department, and someone else only belongs to only 1 department. I'm using the Department field in the user property to set this, using the format; Singe Department: Department 1 Double Department: Department 1, Department 2 So I create a DG with a custom filter which looks at the Department Filed, via the following condition(s); Field Condition Value...

external and internal port numbers
cx Im seting up file sharing between my desktop (host cmputer) and my laptop ...I'm asked to insert external and internal port numbers. Are those the same as the ones I have entered in my email setup?? > cx Im seting up file sharing between my desktop (host cmputer) and my > laptop ..I'm asked to insert external and internal port numbers. Are > those the same as the ones I have entered in my email setup?? No. Different application. If it's the file sharing built into the operating system, you'll want to ask in a newsgroup about that OS. -- f.h. Microsoft O...

Missing Data Options
I am trying to use Tools - Options - Chart. I want to use the "not Plotted" option. When I click on it my chart does not change. This happens after I've selected the chart and then trying to make these changes. What am I doing wrong? I want to create a chart that ignores missing data, and gives me a trend line based on the data I have. I've had my nose in my Excel "bible" but can't find the answer. I greatly appreciate any tips. Jim -- jimsmith888 ------------------------------------------------------------------------ jimsmith888's Profile: http...

Extracting data and returing to colums.
I am about half way there, but I need some more help. These are web inquires that parsed out in the same colum and repeat th same way all down the page. I want to be able to use the fields as guidelines to pull the dat adjacent to it and sent to the new colum. In a way it is transposin all the data, but I need to pull all the first names of these peopl and have them align in the first colum. Same for Last name, business etc. So if the information that I have come across like this in th spreadsheet, I just want the customer infomation to move to th appropriate colums like the third example...