Exporting Access Queries to Specific Excel Worksheets and Columns - Need Help

[orangeman2003]

Hi,

I am hoping someone can take pitty on me for not knowing VBA and getting myself into a mess where I need programming help to solve. I have a DB (access) with numerous table and queries. I have data on 25 different applications and what I need to do is put together an excel workbook where a user can validate data for their system. Basically I want each query to be a tab in the excel workbook. Not hard in principle right. However, I already created the excel template to account for users making updates to each column and this has resulted in an extra blank column to the right of columns in the access query. For example, the first column in my access query might be 'system name' and the second column might be 'system acronym' However, in the excel sheet it would read 'system name' followed by a blank column called 'updated system name'. If the system name from the DB was wrong the user would use that extra excel column to enter the proper name and thus I could see the before/after. Rather than export to Excel and do all of this manual formatting I would like to be able to hit a command button where the logic says:



export system x (where denoted by system ID) from query y and take these fields (a, b, c, e) from query y and put them into x worksheet in x Excel workbook where query field "a" would go into excel column "b" and where query field "b" would go into excel column "d" and so forth. This way I could literally have my validation sheet be created automatically and require no after the fact formatting.

Thank you for any help on this

Evan

Sounds more confusing than it is but I'm totally stuck.

[rpeare]

http://stackoverflow.com/questions/2...cells-in-excel

An example of exporting data to a specific sheet/cell in an excel file

[orangeman2003]

http://stackoverflow.com/questions/2...cells-in-excel

An example of exporting data to a specific sheet/cell in an excel file

yea, I've tried this, earlier today in fact and I can't get it to work without error messages not to mention this is a lot of complicated code for a a true beginner. Thanks though.

[ItsMe]

VBA will probably be the simplest. You probably do not need to create an ADO connection though. The simplest may be to create a query object and have an alias with null or empty string fields for each column. Then do your export based on the query object.

I would start by creating and saving a named query that employs said alias'.

[orangeman2003]

so I found some code that I got to work but it's missing a lot of what I need. this is the code:

Private Sub SaveRecordsetToExcelRange_Click()
' Excel constants:
Const strcXLPath As String = "C:\Users\p780081\Desktop\Book1.xlsx"
Const strcWorksheetName As String = "Sheet1"
Const strcCellAddress As String = "A1"


' Access constants:
Const strcQueryName As String = "qry_system_functions"



' Excel Objects:

Dim objXL As Excel.Application
Dim objWBK As Excel.Workbook
Dim objWS As Excel.Worksheet
Dim objRNG As Excel.Range



' DAO objects:
Dim objDB As DAO.Database
Dim objQDF As DAO.QueryDef
Dim objRS As DAO.Recordset


On Error GoTo Error_Exit_SaveRecordsetToExcelRange

' Open a DAO recordset on the query:

Set objDB = CurrentDb()
Set objQDF = objDB.QueryDefs(strcQueryName)
Set objRS = objQDF.OpenRecordset

' Open Excel and point to the cell where
' the recordset is to be inserted:
Set objXL = New Excel.Application
objXL.Visible = True
Set objWBK = objXL.Workbooks.Open(strcXLPath)
Set objWS = objWBK.Worksheets(strcWorksheetName)
Set objRNG = objWS.Range(strcCellAddress)
objRNG.CopyFromRecordset objRS

' Destroy objects:
GoSub CleanUp

Exit_SaveRecordsetToExcelRange:

Exit Sub

CleanUp:

' Destroy Excel objects:
Set objRNG = Nothing
Set objWS = Nothing
Set objWBK = Nothing
Set objXL = Nothing

' Destroy DAO objects:
If Not objRS Is Nothing Then
objRS.Close
Set objRS = Nothing
End If
Set objQDF = Nothing
Set objDB = Nothing

Return

Error_Exit_SaveRecordsetToExcelRange:

MsgBox "Error " & Err.Number _
& vbNewLine & vbNewLine _
& Err.DESCRIPTION, _
vbExclamation + vbOKOnly, _
"Error Information"

GoSub CleanUp
Resume Exit_SaveRecordsetToExcelRange
End Sub
-------------------------------------------------------

Things I can't figure out how to do:

1) export the header row from my query
2) clear any existing contents in the excel worksheet before executing the transfer
3) choose which columns in my query go into excel (only a select few) and specifically which columns in excel to put them

I was thinking if I could get this to work I'd create a command button for each query I want to export. Ideally though I'd have one command button that would simply say something like 'Export all data' and when clicked it would execute everything at once.

[ItsMe]

I did not look too closely but, that code seems more complicated than necessary.

I would start with a query object that employed alias'. Worst case yo u use VBA to add your columns to excel or use a template with named ranges or...

[rpeare]

Give an example of your source data (as it exists in your query) and an example of what you want your base layout of your excel file template looks like. If you can provide the excel file that you want as your template that would be a help too.

[orangeman2003]

Give an example of your source data (as it exists in your query) and an example of what you want your base layout of your excel file template looks like. If you can provide the excel file that you want as your template that would be a help too.

I can't provide the source data but I can't see any harm in providing the query headings. The actual records themselves would be irrelevant here

the columns in my excel sheet are the names of the fields in my source db table

lets say i have a total of 10 unique systems. basically I need to transpose the information for each system in my db into an excel sheet to then have validated, so in the process of sending to excel a filter would have to be applied on 'system_id' as I'd need to have one file for each system. The idea is that everything in the non highlighted column is the actual value from the source db while the highlighted columns are where a user would update an incorrect record. The problem is I have no idea how to tell access to select a select set of fields from my query and place those fields in their respective columns in excel. Keep in mind that in my example (system exchanges) a single system can have more than one exchange, that is why I need to use the system ID as the filter.

templateexample.ziptemplateexample.zip

[rpeare]

I didn't say provide the source data, I said an example of your source data

based on this example this is what I would do though.

In your source query create a null field for each of your other columns that relate to your spreadsheet.

For instance Let's assume the column headings relate to field names within your database you'd have something like

SELECT [Origin Node], [Sys ID], [Acronym], ... etc. FROM SOURCETABLE

change it to

SELECT [Origin Node], Null as [Updated Node], [Sys ID], [Acronym], ... etc. FROM SOURCETABLE

In essence you're creating blank columns with the headers you need, then all you have to do is bulk export the query to an excel file.

Way easier (and faster) than try to do this line by line, cell by cell.

[orangeman2003]

I didn't say provide the source data, I said an example of your source data

based on this example this is what I would do though.

In your source query create a null field for each of your other columns that relate to your spreadsheet.

For instance Let's assume the column headings relate to field names within your database you'd have something like

SELECT [Origin Node], [Sys ID], [Acronym], ... etc. FROM SOURCETABLE

change it to

SELECT [Origin Node], Null as [Updated Node], [Sys ID], [Acronym], ... etc. FROM SOURCETABLE

In essence you're creating blank columns with the headers you need, then all you have to do is bulk export the query to an excel file.

Way easier (and faster) than try to do this line by line, cell by cell.

yes, this is true, and I just tested it and it works great, I was just hoping to automate some of this. so the one thing where I'm still stuck is that I can either export or simply copy paste but if doing either overwrites my drop down lists in my current excel sheet. How could I paste without doing this. I have a ton of drop downs so it would be pretty time consuming to go back and re-enter them for every sheet in every workbook

Thanks!

[rpeare]

so the example excel file is not a copy of your template....

how about providing an actual template not just a facsimile.

[orangeman2003]

it is but I removed all that stuff for sensitivity. the sample template is the exact same as what I have the difference is that in some of the 'updated' columns I have drop downs to make it easier and faster for the user to update a cell where necessary. Seems like there is no way to fix this if I paste over it

[rpeare]

. . .
It is not the same. If you have combo boxes (what you're calling drop down boxes) in the yellow columns that actually have a limited number of options to choose from your template is NOT the same. If am understanding your process you are exporting a set of data, someone is then taking that spreadsheet and choosing an item from the drop down boxes that's appropriate. Your template does not include the anything that would indicate what those combo boxes are supposed to have in them. it's just a flat excel file. What you actually want, again if I understand it correctly is to export your data, but leave the options in each drop down alone so the user can update them accordingly. Without an example of what you intend those combo boxes to hold and how you've done it I can't help you. again... I am NOT ASKING for your data, an EXAMPLE of the data (particularly the options in your drop down boxes) would be sufficient if it is constructed the same way your actual template is.