Different ways to extract data from Access to Excel

I have been using Access 2003 and 2007 to read and do simple data (not 
design) repairs to old Jet databases that underlie a legacy proprietary data 
collection program my company uses. My latest challenge is to automate the 
extraction of certain data from the tables and put it in an Excel 
spreadsheet. Each time the extraction is done it would involve finding a 
certain table, searching for a record that meets the new criteria in a 
certain field, then moving over to another certain field and extracting the 
text, number, or date therein.

I would really appreciate if anyone could point out which makes more sense: 
(1) to work from Excel and write VBA code to open Access, search the 
database tables, find the needed info, and copy it back to a spreadsheet, 
(2) use Access VBA to do the same, or (3) use an Access query to find the 
information and then export it to Excel? Better to pull or push?  Like a lot 
of things, I suspect there are a lot of ways to accomplish this task, but 
not being an Access expert, maybe there is a good reason you know it would 
be better to do it one way or the other.
Thanks a lot,
Carl 


0
xlcj
12/22/2007 9:13:07 PM
access 16762 articles. 2 followers. Follow

2 Replies
873 Views

Similar Articles

[PageSpeed] 44

Use a query to find the record, then use either TansferSpreadsheet or 
OutputTo to write the data over to Excel. Here's a sample line of code to 
use TransferSpreadsheet:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"qryXTab_ItemCost", "S:\Customers\ItemCost.xls", True
-- 
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"xlcj" <notarealaddress@comcast.net> wrote in message 
news:RLednRW1e_5_4PDanZ2dnUVZ_i2dnZ2d@comcast.com...
>I have been using Access 2003 and 2007 to read and do simple data (not 
>design) repairs to old Jet databases that underlie a legacy proprietary 
>data collection program my company uses. My latest challenge is to automate 
>the extraction of certain data from the tables and put it in an Excel 
>spreadsheet. Each time the extraction is done it would involve finding a 
>certain table, searching for a record that meets the new criteria in a 
>certain field, then moving over to another certain field and extracting the 
>text, number, or date therein.
>
> I would really appreciate if anyone could point out which makes more 
> sense: (1) to work from Excel and write VBA code to open Access, search 
> the database tables, find the needed info, and copy it back to a 
> spreadsheet, (2) use Access VBA to do the same, or (3) use an Access query 
> to find the information and then export it to Excel? Better to pull or 
> push?  Like a lot of things, I suspect there are a lot of ways to 
> accomplish this task, but not being an Access expert, maybe there is a 
> good reason you know it would be better to do it one way or the other.
> Thanks a lot,
> Carl
> 


0
Arvin
12/23/2007 1:59:52 AM
Arvin,
Thanks for your help.
Carl


"Arvin Meyer [MVP]" <a@m.com> wrote in message 
news:%23BySGeQRIHA.3400@TK2MSFTNGP03.phx.gbl...
> Use a query to find the record, then use either TansferSpreadsheet or 
> OutputTo to write the data over to Excel. Here's a sample line of code to 
> use TransferSpreadsheet:
>
> DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
> "qryXTab_ItemCost", "S:\Customers\ItemCost.xls", True
> -- 
> Arvin Meyer, MCP, MVP
> http://www.datastrat.com
> http://www.mvps.org/access
> http://www.accessmvp.com
>
>
> "xlcj" <notarealaddress@comcast.net> wrote in message 
> news:RLednRW1e_5_4PDanZ2dnUVZ_i2dnZ2d@comcast.com...
>>I have been using Access 2003 and 2007 to read and do simple data (not 
>>design) repairs to old Jet databases that underlie a legacy proprietary 
>>data collection program my company uses. My latest challenge is to 
>>automate the extraction of certain data from the tables and put it in an 
>>Excel spreadsheet. Each time the extraction is done it would involve 
>>finding a certain table, searching for a record that meets the new 
>>criteria in a certain field, then moving over to another certain field and 
>>extracting the text, number, or date therein.
>>
>> I would really appreciate if anyone could point out which makes more 
>> sense: (1) to work from Excel and write VBA code to open Access, search 
>> the database tables, find the needed info, and copy it back to a 
>> spreadsheet, (2) use Access VBA to do the same, or (3) use an Access 
>> query to find the information and then export it to Excel? Better to pull 
>> or push?  Like a lot of things, I suspect there are a lot of ways to 
>> accomplish this task, but not being an Access expert, maybe there is a 
>> good reason you know it would be better to do it one way or the other.
>> Thanks a lot,
>> Carl
>>
>
> 


0
xlcj
12/23/2007 7:48:49 PM
Reply:

Similar Artilces:

Excell graphs
Hello, I would like to make a grafh with excell but seem to be very lost. :confused: I would like the graph to go horisontal left to right continous(one bar.) Like a time-line. Thanks for any tips -- dammit1 ------------------------------------------------------------------------ dammit1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=16311 View this thread: http://www.excelforum.com/showthread.php?threadid=278285 By 'time-line', do you mean a gantt chart? http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=343 http://peltiertech...

Excel slow to epen with Network Connection
Hi Excel 2003 has recently become very slow to open. Regardless of the workbook / file. I have disabled all Add-In's and cleaned out the temp folder. The slow down appears to be related to having a live network / internet connection. If I disable my connection or pull out my network cable and launch Excel it opens straight away, if I plug it back in or re-enable it it takes 30-45 seconds to open. I have added Excel to the Windows Firewall exception list but it still takes around 15-25 seconds to open a workbook. Any ideas what its doing? I have read somewhere online about it bef...

Need to combine and add data
I am a sysadmin but I don't have much experience working with excel. My problem is that I have three spreadsheets and they have the same column names but the number are not the same in four of the columns. I need to create one spreadsheet with all of the number added up where another column is the same on each spreadsheet. I am not sure if I am explaining this right so if you need more info please let me know. I am using excel 2003. -- smoked1 ------------------------------------------------------------------------ smoked1's Profile: http://www.excelforum.com/member.php?action=geti...

How to combine data from 2 separate workbooks onto 1 worksheet
I am planning a meeting for 100 attendees. Our database contains a unique ID for each attendee as well as their name, mailing address, phone number, etc. I have another database that I received from our Travel Department containing the airline information (arrival date, arrival time, flight number, arrival airport, etc). How can I merge the 2 databases together into 1 database without having to copy/paste each attendee's information individually. Both databases have the unique ID for each attendee - is there a way to have Excel "find" the unique ID and then ad...

Table relationships in Excel?
Hello, I have an excel file exported from SQL server. There are two tables 1. User (columns: id, name, address, etc) 2. Tasks (columns: id, userid, description) Is there a way to display the user NAME insead of the ID in the tasks table? Many thanks in advance! Saturnius You could do a vlookup on the linked field. If id is the same in both tables use a vlookup function "saturnius" wrote: > Hello, > I have an excel file exported from SQL server. There are two tables > 1. User (columns: id, name, address, etc) > 2. Tasks (columns: id, userid, description) > > Is...

Pulling data daily from a differently named report everyday
Hello, I have a spreadsheet where I use formulas to pull data from multiple reports on a shared server everyday. What I do is to save those reports under a certain name everyday since the formulas have to have the static report name in them to pull data. (For example, A1 may pull data from c:\reportfolder\reportA, B1 may pull from c: \reportfolder2\reportB, etc). In these report folders our IT group runs a new report in it everyday but of course they change the name of the report every day (to reflect the date) . . so in the reportfolder there will be "reportA-3-12-2009", and "...

Data Validation #16
What is the best way to validate data input? I have tried Data>Validation, but can't figure out a way to reject formulas. I have tried using routine in Worksheet Change event but have to use application.undo to keep invalid entry out of Undo list which doesn't allow user to edit erroneous input. Any better ideas? I am fairly new to Excel development. Any help would be greatly appreciated. Thanks, Fred L flambelet@cccneb.edu Fred, Excel doesn't have a built in worksheet function to determine if a cell contains a formula. Therefore, to do so you will need a user def...

Different margins on different pages
I am setting up a template for letters at work. The first page of our letterhead has one ste of margins and all subsequent pages will have a different sent of margins. I know that section breaks can be used, but if staff copy and paste text from existing letters into this new template, the section break moves. Is there any way of locking the section break to the page? so the pasted text goes over the top of it? Hi Lilly80, You could setup your document with a 'different first page' layout. That allows the first page to have a different set of margins to the rest o...

Different values for error bars in series
In Excel 2007, how do you put in different error bar values for each data point in a series? So far I am only able to put in one value for all in my bar chart. Hi, See Jon's blog on the subject. http://peltiertech.com/WordPress/error-bars-in-excel-2007/#comments Cheers Andy On 06/04/2010 15:52, wdwind1 wrote: > In Excel 2007, how do you put in different error bar values for each data > point in a series? So far I am only able to put in one value for all in my > bar chart. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

Excel 2007: No screen refresh
Hello I have a network which around 30 Windows XP pcs, incl. Office 2007 Pro. All updates are installed. Under some circumstance, Excel 2007 will not refresh the screen, for example when Excel was minimized and will maximized. I saw this problem not on all pcs but on some. I did also update the graphic card driver and on one pc I changed also the graphic card. Do you know this problem? Is there a workaround? Kind regards, Martin Schweizer ...

MS Access and external data
In Excel, when trying to "get external data" from an MS Access database on a Win2K server, in a shared folder, I receive an MSQUERY error. When I copy the file to my desktop and to to import the data, it works fine. It seems to be something about being on the network. Any ideas??? ...

what's the best way to depict a bell curve?
I want to depict a smooth bell curve . . . not a pyramid-shaped line. I suppose one way would be to increase the number of data points, but I don't want to do that. Am I missing something simple? The simple things that you are missing are to tell us: a what you've tried and b why you are reluctant to use a few extra data points. -- David Biddulph raybrag wrote: > I want to depict a smooth bell curve . . . not a pyramid-shaped line. > I suppose one way would be to increase the number of data points, but > I don't want to do that. Am I missing something simple...

Can't save Excel using Save button,
When I try to save an Excel spreadsheet, I get a message saying "GWXL97.xla could not be found. Check spelling of file name and verify that the file location is correct." I have never named any of files by that name and don't know what it refers to. I have to hit the "X" button on the upper right hand corner and wait for it to ask me if I want to save it, then go through the save process. This happens even if I try to save changes to an existing excel document This may help... xl: close button is dimmed after groupwise is installed http://support.microsoft.com...

A Different CRM for GP
Hello, We are pondering the idea of implementing MS Dynamics CRM. However, I am curious whether anyone else has had success in integrating a different CRM product with GP 10.0. It's not that we don't like the MS product; I just like to ensure I have covered all bases. Our partner does not have any other recommendations as they have not worked with any other CRM applications. Any information would be greatly apprecaited. Thank you, -- Jessie GoldMine is a good product, very mature, and easier to maintain table structures. There are links to GP but I cannot remember the fir...

Unable to open embedded word document from Excel
Hi, I have Excel 2003 (SP3) installed on my computer. In an Excel file, I inserted a word object (created from file, display as icon) and the insertion worked fine. When I try to open my word document from Excel, nothing happens. No error message and the file doesn't open. It is like if my file icon is simply a draw. When I open the same Excel file with another computer(Excel 2003), I can open my word embedded object easily. In fact, I can insert Excel or txt object in Word document without any problem but everything I insert in Excel doesn't work (word, txt, et...

character spacing Excel 2003
How does one get all numbers in a column to have set spacing instead of proportional spacing? I remember setting proportional or fixed back in the days of DOS, and haven't used it since and I'm unable to find it in Microsofts online help for either Excel or Word (2003). Try using a fixed width font like Courier or Courier New. -- Biff Microsoft Excel MVP "Joe" <JOsterheim@gmail.com> wrote in message news:nfvtf3t6lq188pkavtqadelnas3a5i28be@4ax.com... > How does one get all numbers in a column to have set spacing instead > of proportional spacing? I remember...

2 users access calendar online with different rights
hi user A wants to give user B permission to see, add and edit entries in calendar online http://www.exchangedomain.ch/usersname/kalender/ user B is able to see, but not to add or edit entries at the moment in outlook itself its working. user B can open the folder calendar of user A and make entries and change entries what have to be done that this is possible also with web access exchange ? thankx mike schwarz On Thu, 26 Oct 2006 14:09:29 +0200, "Mike Schwarz" <ctek@ctek.ch> wrote: >hi > >user A wants to give user B permission to see, add and edit entries in &...

Same column, different cell width at different row
I have tried inserting a break and split, didn't work. I just want to separate the top half of a page with the bottom half so I can apply different cell width on the same column. Or how would I be able to do this? Same column but different cell width. Thanks! Glenn You can't do that. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Glenn Chung" <GlennChung@discussions.microsoft.com> wrote in message news:20746997-9D99-4684-A352-120370D27036@microsoft.com... >I have tried inserting a break and split, di...

data validation #32
I have a cell whose value must be 7 numeric digits and the leading zero must be visible. When the cell is empty then I need it to be blank no 7 zeros. I tried using data validation where the text = 7 digits bu that allowed me to enter non numeric characters. I also tried usin data validation where the number must be between 0000001 and 999999 but that did not show the leading zeros. Any suggestions?? Thanks -- cparson ----------------------------------------------------------------------- cparsons's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1286 View thi...

Fixed Assets
When using the Fixed Asset module, I am finding some small differences when compared to the Depreciation calculated by the External Accountants for tax purposes. Client would like to match exactly to the amounts calculated by external accounts. What is the best way to fix this issue? thanks, -- Patti Need more info Patti. What are the depreciation methods/settings that are creating the differences? Does this occur on every asset or just some? Frank Hamelly MCP-GP, MCT, MVP East Coast Dynamics www.eastcoast-dynamics.com get your gptip42today at www.gp2themax.blogs...

Dates Difference
Hi, I need to get the difference between 2 dates (in number of days), how do i get that in vc++? I have used the DateDiff function in asp but couldn't find anything like that. Also, how can i convert string to a date? thanks in advance karan >I need to get the difference between 2 dates (in number of days), how >do i get that in vc++? Karan, Convert (if the dates aren't already in this format) the dates/times to FILETIME and subtract the values. >Also, how can i convert string to a date? Try COleDateTime::ParseDateTime or VarDateFromStr. Dave -- MVP VC++ FAQ: http://ww...

how i do enable icons in excel
some of icons doesnt work like chart , scenario ... when i go to customise its appear active when i closed its back to an active how can slove this problem thanks salah If the workbook is shared Insert>Chart and Tools>Scenarios will be grayed out(unavailable). Check at Tools>Share Workbook to see if book is shared. Gord Dibben Excel MVP On Sun, 24 Apr 2005 03:24:07 -0700, "salah" <salah@discussions.microsoft.com> wrote: >some of icons doesnt work like chart , scenario ... > >when i go to customise its appear active when i closed its back to an activ...

Accessing Web Service through a Proxy.
Hello, I have an unmanaged VC7 app (ActiveX control implemented using MFC) calling a C# dotnet service. I'm using the VS2003 generated proxy class derived from CSoapSocketClientT. The activeX control works fine but when used through a proxy that requires authentication I end up getting 407 error. I tried using authentication schemes as suggested in most of the threads. CAtlHttpClient& httpClient = webserviceInst.m_socket; CSampleBasicAuth basicAuth; httpClient.AddAuthObj(_T("BASIC"), &basicAuth, &basicAuth); CNTLMAuthObject ntlmAuth; httpClient.AddAuthObj(_T("...

trying to ensure that Access closes down
I'm running Access 2003 in a multi-user environment, and I've been using the Timer event in the Main Menu form that's always open to close down all open front end mdb files at the end of the day in order to compact and repair the back end mdb file. I do this by renaming the extension of a file on the network drive from ".no" to ".yes". The code in the form's Timer event checks to see if the file named "close_app.yes" exists, and if it does, it closes down the user's front end application. So I can make sure all the users will c...

game in excel #2
hi there guys is any body aware of a car racin game in excel. i have tried it o 2000. but it is not working in 2003 -- manoo ----------------------------------------------------------------------- manoob's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2952 View this thread: http://www.excelforum.com/showthread.php?threadid=49226 Never heard of any games for excel. send it to me >>> excelmarksway@yahoo.com.au "manoob" wrote: > > hi there guys > > is any body aware of a car racin game in excel. i have tried it on > 2000....