473,320 Members | 1,978 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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.
Feb 13 '19 #1
1 1974
twinnyfo
3,653 Expert Mod 2GB
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.
Feb 14 '19 #2

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

Similar topics

1
by: Chris Beach | last post by:
Hi, I have a JSP page with several forms on it. Some of these forms are generated dynamically, and each of them submits some information to a database. Handling one form is easy, as I can...
1
by: Nicolae Fieraru | last post by:
Hi All, I want to find if there is a different way than the way I am working now. Lets say I have a table, tblCustomers containing address details. I want a report with all the customers from...
0
by: Bisbal | last post by:
Hi All, I have created a class that simulates a MDI form by putting the 'child' inside a panel located in the 'parent' form. I had to do this because we use a custom UI that doesn't work...
10
by: Sebarry | last post by:
Hi, I'm using the code at http://the-stickman.com/web-development/javascript/upload-multiple-files-with-a-single-file-element/ to upload multiple files with a single file element. It works fine...
1
by: mrbadboy | last post by:
Hi, I need to submit multiple forms with using single button. I've mentioned my code below. <? if(isset($_POST)){ print_r($_POST); } ?> <script type="text/javascript">
1
by: veer | last post by:
Hi i made program on searching and if a word is present in a file more than one time this program search it one time and exit the file but i want to show all the locations of the searched word in...
8
pradeepjain
by: pradeepjain | last post by:
hii guys, i am working on a site where in guests can say whether the answer / review was useful for them ... there will be many review / answers in a single page .. so how to go on...
29
by: michelle copper | last post by:
I have codes where I can export multiple queries to one single excel workbook but the problem with my code is that it does not ask me where I would like them to be exported to. Like to my desktop or...
2
by: iainharvey | last post by:
Hi I have a query that I want to export multiple fixed-width text files from. There needs to be as many files as there are unique entries in a given field and the file name of each new exported...
1
by: Curiuos1 | last post by:
working on providing reporting for one of the projects and need to export multiple forms from access to one excel file with multiple tabs. Thank you for your help.
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.