Import external data from Access query with Variable

Hello - I have been able to record a macro where I can change the 
"inventory.Area" from US006 to US007.  I would like to be able to make the 
area a variable and pick up the area code from within a spreadsheet.

For example, I am doing reporting for 10 areas and would like to have one 
template that pulls in the data from Access for one area, saves the file, 
goes to the next area, pulls in that data from Access and so on.  But I 
cannot figure out how to make the area in the VBcode below a variable.

Any help would be appreciated.

Sub Macro3()
    With Selection.QueryTable
        .Connection = Array(Array( _
        "ODBC;DSN=MS Access Database;DBQ=C:\Documents and 
Settings\ruffnro\My Documents\__Reporting\Monthly Reporting\_ Financial 
Reporting.m" _
        ), Array( _
        "db;DefaultDir=C:\Documents and Settings\ruffnro\My 
Documents\__Reporting\Monthly Reporting;DriverId=25;FIL=MS Access;MaxBufferS" 
_
        ), Array("ize=2048;PageTimeout=5;"))
        .CommandText = Array( _
        "SELECT inventory.Area, inventory.`Client No`, inventory.`Client 
Name`, inventory.SEC, inventory.`CP Name`, inventory.`Net Unbilled`, 
inventory.`Net Billed`, inventory.`net Invty`" & Chr(13) & "" & Chr(10) & 
"FROM inventory inven" _
        , "tory" & Chr(13) & "" & Chr(10) & "WHERE (inventory.Area='US007')")
        .Refresh BackgroundQuery:=False
    End With
End Sub
0
Utf
11/24/2009 7:41:01 PM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
746 Views

Similar Articles

[PageSpeed] 54

Dim sTheArea As String
sTheArea = ActiveSheet.Range("A1").Value

"WHERE (inventory.Area='" & sTheArea & "')")

"ruffnro" wrote:

> Hello - I have been able to record a macro where I can change the 
> "inventory.Area" from US006 to US007.  I would like to be able to make the 
> area a variable and pick up the area code from within a spreadsheet.
> 
> For example, I am doing reporting for 10 areas and would like to have one 
> template that pulls in the data from Access for one area, saves the file, 
> goes to the next area, pulls in that data from Access and so on.  But I 
> cannot figure out how to make the area in the VBcode below a variable.
> 
> Any help would be appreciated.
> 
> Sub Macro3()
>     With Selection.QueryTable
>         .Connection = Array(Array( _
>         "ODBC;DSN=MS Access Database;DBQ=C:\Documents and 
> Settings\ruffnro\My Documents\__Reporting\Monthly Reporting\_ Financial 
> Reporting.m" _
>         ), Array( _
>         "db;DefaultDir=C:\Documents and Settings\ruffnro\My 
> Documents\__Reporting\Monthly Reporting;DriverId=25;FIL=MS Access;MaxBufferS" 
> _
>         ), Array("ize=2048;PageTimeout=5;"))
>         .CommandText = Array( _
>         "SELECT inventory.Area, inventory.`Client No`, inventory.`Client 
> Name`, inventory.SEC, inventory.`CP Name`, inventory.`Net Unbilled`, 
> inventory.`Net Billed`, inventory.`net Invty`" & Chr(13) & "" & Chr(10) & 
> "FROM inventory inven" _
>         , "tory" & Chr(13) & "" & Chr(10) & "WHERE (inventory.Area='US007')")
>         .Refresh BackgroundQuery:=False
>     End With
> End Sub
0
Utf
11/24/2009 7:59:06 PM
Reply:

Similar Artilces:

How to transform HTTP query string (HTML data) to xml?
Dear experts, I need to send a simple HTML form to an ASP.NET page which has to create a xml object. What technique would you recommend me to use in order to transform a HTML form data to xml? What naming convention for the input fields would be best so that I can easily parse the query string in the asp page and create the xml object? I also dispose of the .xsd schema of the xml that must be created if that could be of some use. At every request the .xsd could change so I need some general algorithm. I would greatly appreciate your help. ...

Cannot show, import, export userform: error &H80004005 / component is not corretly installed
Please, help me! I can not find where is the the information I need. My problem is: I had Windows 2000 and Office 2000. Now they installed Windows XP and Office 2000 (serv.pack 3). And my forms do not work anymore! The error message says nothing useful, only: 1) the component is not corretly installed (ok! but it's a common form with only normal windows common buttons and labels, not a single userparty creative control) 2) error: &H80004005 -2147467259 3) memory insuficient (ahah, at 512 Mb, only Excel running... ) 4) can not set OleObjectBlob property But if I try to open the...

checkbox as query criteria
Hi, in an Access form i've put a combo box that has a query as data source. On this form I'd like to add a check box that, when it is selected, adds the "is not null" criteria on a query field. There is a simple way to do this? Thank you very much for help. -- Remigio www.sacraspina.it www.amicitosondoro.it www.icmonteodorisio.it www.parrocchiacupello.it www.cralnuovainiziativa.it www.associazionehistonium.it On Sat, 1 May 2010 05:55:14 -0700 (PDT), remigio <linoreale@gmail.com> wrote: Create two queries. Then switch between them based on your n...

SessionClass.Variable problems
As I understand it, this array of 10 is an array of objects, and it is used to store objects between custom dll's. Anyways I have written one, and what I put in to the array doesnt seems to stay there. (I am developing in VB6, NOT vb.net) ****Code**** Public Function Process(Session As QSRules.SessionClass) As Boolean Dim cartList As CartArray 'Custom object Dim X As Integer X = SessionClass.Variable(0) MsgBox("Variable(0): " & X) cartList = SessionClass.Variable(1) if(cartList = Null) then set cartList = new CartArray cartList.init end if 'A bunch of cod...

Set a range from a variable location
I am using a loop function that will eventually select and activate a specific cell known as "TheCurrentCell." Once I reach that location I want to use that coordinate as the upper left area of a new range. Supposing that the end cell of my range is D1000, the Range selection would be something like Range(TheCurrentCell:D1000).select, however, I don't know the correct syntax to make this happen. Please help. Thank you. Use something like Range(Selection.Address & ":D1000").Select. "Troubled User" wrote: > I am using a loop function that will...

converting tabular structures in a Word document into an actual table or reading data from the tabular structures using VBA code
I have a macro which can read the last cell/column of all tables in a Word 2003/2007 document and store the data in an MS-Access table. But, some Word documents have the data in structures like a table format but are not actually tables. The structure looks like a table, but the table borders are actually line connectors. These documents were created by a software(VeryPDF PDF to Word converter) which converted the PDF documents(the original format these documents were) into Word documents. 1. Is there a way I can convert/replace the tabular structures with actual tables in Word so t...

Twist Data?...
Hi all... I've got the following in a dataset: 19 30 1200 FI FI 20030906 36 19 30 1324 FI FI 20030906 36 and I want the following result: 19 30 1200, 1324 FI FI 20030906 26 Any guess on how to do this? thanks much!... Hmmm... Not quite clear: The data is arranged in columns, I assume It looks as if both items in the same column are the same, you want just one item, otherwise the one in the top row first, then the one from the bottom row. But what about the last 36s that should yield 26? Typo? Does the dataset have more columns or more rows or both? And how big is it?...

moving payables data from open to history
Hello: A client says that someone imported data about a year or two ago into Great Plains from their AS400. Many payables documents that were imported should have been coded during the import as open, instead of history. The client knows that she can take care of this herself within two hours, by simply turning off the posting to the GL and entering and posting the payables documents to move them to history. But, she is wondering if there is a quick and easy way to do this on the back-end. I'm familiar with the open and history payables tables within GP. And, I know through a T...

is there anyway to access funcion written in c by vc++
hi Is this is possible to access ,a function written in C by VC++. Arbind >Is this is possible to access ,a function written in C by VC++. Yes. I suspect you need to know about using extern "C" to tell the compiler that your function is 'C' rather than C++. For example: extern "C" int MyFn( int p1 ); You often use it like this: extern "C" { #include "Your_C_Header.g" } Dave -- MVP VC++ FAQ: http://www.mvps.org/vcfaq >#include "Your_C_Header.g" or even #include "Your_C_Header.h" who put that g key in the wa...

Insert,Update Data in sage (MS Access Linked tables) using Vb.net form
Hi folks, I am developing application using vb.net which requires integration with SAGE LINE 50 (Accounting software ) V11... The data which SAGE is using is MC ACCESS 2003 database... with linked tables in it... Now I Have developed the Sage connection using ODBC which works fine when reading the record but cannot Add or Update record into the Linked tables.... When i debug the program the error is at the line where it has... <br> MyodbcCommand.ExecutenonQuery() <br> Can anybody Help ????? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/acce...

merging 2 cells without losing data?
How can I merge 2 cells without losing data from the other cell? Hi Bob Not possible I'm afraid. Try placing the dat from both cells into one and use "Center across selection" under Format>Cells>Alignment Merge cells always end up causing grief. they are best avoided. ***** Posted via: http://www.ozgrid.com Excel Templates, Training & Add-ins. Free Excel Forum http://www.ozgrid.com/forum ***** "bob" <bobree@hotmail.com> wrote in message news:%23JuOM9HGEHA.2308@tk2msftngp13.phx.gbl... > How can I merge 2 cells without losing data from the other...

Transformation of data into columns
Hi, I have the data from a flattened spreadsheet in a table in the following form: f1 f2 f3 period to: Scheme1 Scheme2 31/01/2005 Net Gross 28/02/2005 Net Gross 31/03/2005 Net Gross 30/04/2005 Net Gross 31/05/2005 Net Gross 30/06/2005 Net Gross 31/0...

Accessing MIcrosoft Outlook
Yesterday, when I tried to access Outlook in my home pC, I received the following message: Cannot open MIcrosoft Office Outlook. Cannot open the Outlook window. The set of folders cannot be opened. The file C:|Users \jillandgordon\AppData\Local\Microsoft\Outlook\Outlook.pst is not a personal folders file. Does anyone have any ideas as to how I can fix this? Masny thanks, Gordon in TX Try this first http://support.microsoft.com/kb/287497/ "Gordon McDougall" <braidwoodboy@gmail.com> wrote in message news:d0e17ac8-0215-431c-971f-bd862b58a245@y17g2000yqd....

How can I sort duplicate text data in excel?
I have a large list of noames that I need to make sure that none of them are duplicated. Is there a way to have excel check it quisker than me reading every name until I find a duplicate? After selecting your data go to filter Advanced filter and check "Unique records only" You can even copy it to another area all uniques entries if you want to ... "TinaScheu" <TinaScheu@discussions.microsoft.com> wrote in message news:0399D580-7E69-4DF0-A969-E7FC5F777C70@microsoft.com... >I have a large list of noames that I need to make sure that none of them >are >...

Problems Converting Data from Quicken 2001 Deluxe to MS Money
Hello, I have a relatively new Compaq Desktop (2.5 GHz Celeron with 512 MB RAM). I have a Viewsonic Pocket PC and I wanted to use it to track my financial data so I purchased Money 2003 Standard. I tried several times to convert my Qucken Data (it's a big file--I've been using Quicken since 1995). My Quicken program is Quicken 2001 Deluxe. Anyway, the MS Money program started to convert and after a few minutes said: "Your Quicken file could not be converted. Money could not convert your Quicken file. You might have run out of disk space or system memory. Try closing othe...

I do not have access to the D: drive from Publisher, why?
I do not have access to the D: drive from Publisher, why? It keeps saying "incorrect function" khaganman <khaganman@discussions.microsoft.com> was very recently heard to utter: > I do not have access to the D: drive from Publisher, why? It keeps > saying "incorrect function" Is the D: drive your CD drive? -- Ed Bennett - MVP Microsoft Publisher ...

Accessing my information offline
I have updated my Outlook to 2002 on my notebook computer and when it is connected on the Lan at my office(new ethernet connection) I can see my calendar, addressbook, e- mails etc. However, when I disconnect and bring my notebook home, and try to access calendar, etc, it says I must sychronize while I am still connected to the Lan. My previous version of outlook didn't have this problem. I synched it on the Lan and it worked. This one won't. Any ideas???? How do you have your folders marked for Offline use under tools->Send/Receive settings? --� Milly Staples [MVP - Outlo...

Data Validation List not showing
I'm using Excel 2003. My data validation lists have stopped working on one sheet in my workbook. It is working on all other sheets. I have googled the problem and found the following advice: 1. Make sure freeze panes is off.... check. 2. Select "Show All" under Tools->Options->View->Objects ... check. The problem remains. Any ideas? "Stopped working" doesn't do much to describe your problem. When you select one of the "stopped working" Data Validation cells, do you see the drop-down arrow to the right of the cell? When you select t...

Trying to create an Update query based on HR data to find upline V
Hi All, looking for some advice. I have an HR table that contains employee information but does not contain management chain info. Basically i am trying to determine who the employees upline VP is. The fields i have to work with are [Employee Name], [Manager Name] and [Job Title]. I figure the logic would be to check the employees' manager and if the manager is a VP (based on job title), return the manager's name to a field called [VP]. If the manager is not a VP then check that manager's manager, so on and so forth until a VP is found. Any ideas would be much appr...

Importing spam list
Hi, I have a long list of spam email addresses that I want to import into outlook. How do I do this? Do they have to be separated by commas? Thaks, Paul Where is this spam list generated? What version of Outlook? Where do you propose to import them? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, Paul Ellis asked: | Hi, | I have a long list of spam email addresses...

Any FREAKIN' way to import DBX files into Outlook 2003
I've tried: Importing via Outlook | Import from another Program or File Importing via Outlook | Import Internet Mail and Addresses Exporting from Outlook Express Tried Many, many times... Can Microsucks make this any more complicated... It's a FREAKIN' DBX file collection NO - No other Application has it Open. YES - The Files ARE there YES - the Internet Account IS there Yes - I've wasted more of my time IMPORTING into Outlook Express in VISTA just to RE-EXPORT back to Outlook. What a bunch of freakin' idiots... Another 2 hours wasted - because one Microsoft applica...

Querying multiple records in two tables
Hi, in my database I have tables for users (UserID, UserName), projects (ProjectID, Project Name), and qualifications (QualID, QualName). I have join tables for users_qualifications (UserID, QualID), and projects_qualifications. (ProjectID, QualID). What I need to do is run a query for a project to show which users have the exact matching qualificiations. Users can have many qualifications, projects can require many qualifications, users may only work on a project if the qualifications required/held match exactly. Please help. Assuming that ProjID, and QualID are numeric, the following sho...

"the wizard is unable to open your query in datasheet mode, possi
"the wizard is unable to open your query in datasheet mode, possibly because another user has a source table open in exclusive mode. your query will be opened in design view" what this message mean ,and how can i solve it Hi - Please provide more details as to what you are doing when you get the error, what your database setup is (i.e. split Fe/BE?, multiuser?, version? etc). Without more information, we can only guess. Thanks - John amr wrote: >"the wizard is unable to open your query in datasheet mode, possibly because >another user has a ...

Program Trying to Access Email Addresses #3
Outlook 2002. When I try to create new mail, forward, or reply I get a dialog box telling me: A program is trying to access e-mail addresses you have stored in Outlook. Do you want to allow this? If this is unexpected, it may be a virus and you should choose "No". How do I determine what program is trying to access the Outlook addresses? I have up to date Norton Anti-virus definitions and have done a system scan, with nothing detected. Thanks for the help. ...

Adding extra data options
Is there a way to customize CRM to allow for adding another heading? I would like to add a second field similar to topic and would like to call it type. Can you add extra data fileds and types in CRM 3.0? You can add extra data fields to an entity. Go to entities customization at setting area. -- Marco Amoedo Plain Concepts http://geeks.ms/blogs/marco/ "xxdcmast" escribió: > Is there a way to customize CRM to allow for adding another heading? I would > like to add a second field similar to topic and would like to call it type. > > Can you add extra data ...