Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access from Excel
I have an extensive Access database that folks are always wanting to export
to excel. I've set up a table of query names/descriptions for export queries I've developed. Users log in, open a form (using switchboard), and select a query description they want, point to an excel spreadsheet where they want it exported, then click a button, it gets exported using the access transferspreadsheet method. I'd like to reverse the process. I'd prefer to keep people out of my db and have 'em "suck" those query results into a spreadsheet instead. I'm sure I can do this, but I need some structural advice. I'm comfortable with vba, though not a whole lot of work in excel vba (Access, and believe it or not...Project vba.) So, presumably, I'd create some selection table by sucking in the already existing table from Access, then use that to select the query the user wants, then pull that query into a sheet by itself. Strategies? -- Jim |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access from Excel
hi,
you could use Microsof query. no vb required or not a lot. on the menu bar.. dataget external datanew database query database type = microsoft access brouse to your data base. follow the wizard. on the last screen of the wizzard are options. 1. return data to xl take you and the data back to xl 2 view data in MSQ take you into microsoft query. you will find it remarkably similar to access. if you already have the querys written in access, it would be better to suck the query results into xl instead of linking to the access tables and rewriting the query in microsoft query(MSQ). if you don't have the querys written in access, it would be better to write them in access instead of MSQ becasue access is a tad bit more sophistacated than MSQ. you can refresh the MSQ with worksheet open event. Private Sub Workbook_Open() Sheets("sheet1").Range("A1"). _ QueryTable.Refresh BackgroundQuery:=False End Sub or as my users perfered, from a command button on the sheet with the above code. that way they could refresh as frequently as they wanted ie refresh, do some work then refresh again to see if anything changed on them. in excel you can put formulas on the sides and at the bottom of the MSQ range but you cannot add rows or columns to the MSQ range. if you do, xl will throws up error messages at refresh. xl views the MSQ range as a named range and will look something like. Query_from_MSAccess_Database sheet1 or something. your users(like mine) will want to archive certain (or all) query runs. if they do a file saveas, they will be saving the file, the query, button....all. and the archive will be refreshable. so i added a second button..save query. Sub macSaveRange() ActiveSheet.UsedRange.Copy Workbooks.Add Range("A1").PasteSpecial xlPasteAll ActiveWorkbook.Names("name").Delete Application.Dialogs(xlDialogSaveAs).Show End Sub copies the query, create a new workbook, pastes the data, deletes the query in the new workbook then show the file saveas dialog. I was managing 30+ MSQ's at one time. i created an xl file to list them. file name, access query or table use and other stuff. try it. you'll like it. regards FSt1 "JimS" wrote: I have an extensive Access database that folks are always wanting to export to excel. I've set up a table of query names/descriptions for export queries I've developed. Users log in, open a form (using switchboard), and select a query description they want, point to an excel spreadsheet where they want it exported, then click a button, it gets exported using the access transferspreadsheet method. I'd like to reverse the process. I'd prefer to keep people out of my db and have 'em "suck" those query results into a spreadsheet instead. I'm sure I can do this, but I need some structural advice. I'm comfortable with vba, though not a whole lot of work in excel vba (Access, and believe it or not...Project vba.) So, presumably, I'd create some selection table by sucking in the already existing table from Access, then use that to select the query the user wants, then pull that query into a sheet by itself. Strategies? -- Jim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trust Access to Visual Basic Project - Access to Excel and back | Excel Programming | |||
How do I access the access data via Excel 2002 with auto update ? | Excel Programming | |||
Importing data from Access to Excel, but I need to vary the table from Access | Excel Programming | |||
export access to excel. change access & update excel at same time | Excel Discussion (Misc queries) | |||
Getting Access Error Messages when running Access through Excel | Excel Programming |