424,279 Members | 1,893 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,279 IT Pros & Developers. It's quick & easy.

Export multiple forms to excel, combine to single file???

P: 1
Alright, so, for starters, your forum here is great and I have learned a lot and have searched EVERYWHERE and read EVERYTHING POSSIBLE in order to try to figure this out.

What I have going on:
I have a database with 2 sharepoint linked tables. One is the base data "Person", the other is the status "Status", only used when needed (i.e. someone inputs a record in a form object that relates to that table). I then have a cubic ton of queries that sort the data from the primary data. It looks at 2 dates and 1 number from the table and determines if one or both dates fall in a certain time period and/or if that 1 number is "3" and sorts accordingly and is broken up based on the "department" that it belongs to. 8 departments, 7 different queries per department to sort, plus not matched/update/append/delete queries. I then have a datasheet per query. All datasheets have 3 combo boxes with multiple choices in each, which save that data to the "STATUS" table as an integer. Each datasheet is also formatted, has locked fields, and cleaned up headings that didn't make sense because coding.

What I want to do:
I want to be able to create buttons so that when clicked, all 7 datasheets belonging to the respective department export to an excel file and look like they do on the datasheets.

What I know:
Docmd.transferspreadsheet doesn't work with Forms, does with queries, however the formatting is trash and my comboboxes show up as an integer, and my code headers make no sense. However, it is possible to get multiple queries into one excel file.
DoCmd.OutputTo works with forms, however, I cannot get them to save to 1 excel file, and I am using code for a save as function so it will only save 1 form then tell me the format is not available. Below is my current, not working for outputting multiple form, stolen and altered from BYTES.COM, code.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub cmdTest_Click()
  5. On Error GoTo Err_cmdTest_Click
  6. Dim cDlg As New CommonDialogAPI         'Instantiate CommonDialog
  7. Dim lngFormHwnd As Long
  8. Dim lngAppInstance As Long
  9. Dim strInitDir As String
  10. Dim strFileFilter As String
  11. Dim lngResult As Long
  12. Const conOBJECT_TO_EXPORT As String = "DepartmentReports"
  13.  
  14. lngFormHwnd = Me.Hwnd                           'Form Handle
  15. lngAppInstance = Application.hWndAccessApp      'Application Handle
  16. strInitDir = CurrentProject.Path                'Initial Directory - [UD]
  17.  
  18. 'Create any Filters here - [UD]
  19. strFileFilter = "Excel Files (*.xls)" & Chr(0) & "*.xls"
  20.  
  21. lngResult = cDlg.SaveFileDialog(lngFormHwnd, _
  22.             lngAppInstance, strInitDir, strFileFilter)
  23.  
  24. If cDlg.GetStatus = True Then
  25.   'MsgBox "You chose the Filename of: " & cDlg.GetName    'Retrieve Filename - [UD]
  26.  
  27.   DoCmd.OutputTo acOutputForm, "POverdue", "Excel97-Excel2003Workbook(*.xls)", cDlg.GetName, False, "", , acExportQualityPrint
  28. DoCmd.OutputTo acOutputForm, "PComingDue", "Excel97-Excel2003Workbook(*.xls)", cDlg.GetName, False, "", , acExportQualityPrint
  29. DoCmd.OutputTo acOutputForm, "DOverdue", "excel97-excel2003workbook(.xls)", cDlg.GetName, False, "", , acExportQualityPrint
  30. 'et al  Keep copy/paste and change form name to add more.
  31. Else
  32.   'MsgBox "No file chosen."      '[UD]
  33.     Exit Sub
  34. End If
  35.  
  36.  MsgBox "[" & conOBJECT_TO_EXPORT & "] has been Exported to " & cDlg.GetName
  37.  
  38. Exit_cmdTest_Click:
  39.   Exit Sub
  40.  
  41. Err_cmdTest_Click:
  42.   MsgBox Err.Description, vbExclamation, "Error in cmdTest_Click()"
  43.     Resume Exit_cmdTest_Click
  44. End Sub
  45.  
My knowledge level of code is mediocre, so bear with me, but any help is greatly appreciated. Even if there is a way to write code that would take whatever they just saved and combine them after the fact and save as a consolidated file, I am open to suggestions.
6 Days Ago #1
Share this Question
Share on Google+
1 Reply


twinnyfo
Expert Mod 2.5K+
P: 2,886
ONe thing for your export codes that don’t make sense—if you convert them to values that you do understand, then you can export those values. For example, if you have a an exported code for “EmployeeType” that uses a combo box that uses tblEmployeeTypes as it’s Row Source, you may see the value of “Full-Time”, “Part-Time”, etc., but the VALUE of that Combo Box is 1, 2, etc. In your Query, just join the field for EmplyeeType to the table tblEmployeeTypes and use the text value of EmployeeType in the output field of your query.

This, of course, is just given for an example. You will need to adjust for your actual data.

Concerning saving to one spreadsheet, you may have to create a TEMPLATE spreadsheet that contains the separate worksheets you need, and then export the data using the Recordset (NOT using TrandferSpreadsheet). There are many ways to do this—but at this point, rather than trying to write code out of thin air, I want to afford you several options and opportunity to discuss the benefits of looking at things a different way.
5 Days Ago #2

Post your reply

Sign in to post your reply or Sign up for a free account.