By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,251 Members | 2,727 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,251 IT Pros & Developers. It's quick & easy.

Transfer data from a sub form and main form to excel spreadsheets

P: 2
Hi, i have a form with the main details of a vendor and and contract description and a budget reference(PK). Within this form, i have a sub form which includes cost details for that specific vendor but also has the same budget reference hence the link. I would like to create a command button to be able to export the data from the main form to an excel spreadsheet and the data from the sub form to excel spreadsheet tab2 from the click of a button. Please help some genius out there that understands this problem. Here is what i have come up with so far: My query filters the main form and extracts the vendor details filtered by [Bud Ref].
This code does not fully work as i get an error message and it just opens the template i have designed but blank. Also it has a problem with my sSQL statement.

Expand|Select|Wrap|Line Numbers
  1. Private Function ExportQuery() As String
  2. On Error GoTo err_Handler
  3.  
  4.  
  5.  
  6. Dim appExcel As Excel.Application
  7. Dim wbk As Excel.Workbook
  8. Dim wks As Excel.Worksheet
  9. Dim wks2 As Excel.Worksheet
  10.  
  11. Dim sTemplate As String
  12. Dim sTempFile As String
  13. Dim sOutput As String
  14.  
  15. Dim dbs As DAO.Database
  16. Dim rst As DAO.Recordset
  17. Dim sSQL As String
  18. Dim lRecords As Long
  19. Dim iRow As Integer
  20. Dim iCol As Integer
  21. Dim iFld As Integer
  22.  
  23. Const cTabOne As Byte = 1
  24. Const cTabTwo As Byte = 2
  25. Const cStartRow As Byte = 3
  26. Const cStartColumn As Byte = 1
  27.  
  28. DoCmd.Hourglass True
  29. Excel.Application.SetOption "Error Tapping", 0
  30.  
  31.     sTemplate = "J:\2007\Databases\Contracts\ContractsForm.xls"
  32.     sOutput = "J:\2007\Databases\Contracts\ContractsFormTest.xls"
  33.  
  34. If Dir(sOutput) <> "" Then Kill sOutput
  35.  
  36. FileCopy sTemplate, sOutput
  37.  
  38. Set appExcel = New Excel.Application
  39. appExcel.Visible = True
  40. Set wbk = appExcel.Workbooks.Open(sOutput)
  41.  
  42. 'sSQL = "SELECT tblContracts.[Bud Ref], tblContracts.Vendor, tblContracts.[Contract Description], tblContracts.ApplicationSystemService, tblContracts.[Contract Type], tblContracts.[Cost Centre], tblContracts.HNC FROM tblContracts WHERE (((tblContracts.[Bud Ref])=[Forms]![frmContracts]![Bud Ref]))"
  43. sSQL = "SELECT * FROM tblDashboard"
  44.  
  45. 'DoCmd.OpenQuery "qryContracts", acViewDesign, acEdit
  46. 'DoCmd.OutputTo acOutputQuery, "", "MicrosoftExcel(.xls)", "ContractsForm", True
  47. 'DoCmd.SetWarnings False
  48. 'DoCmd.Close acQuery, "qryContracts", acSaveYes
  49.  
  50.  
  51.  
  52.  
  53. Set dbs = CurrentDb
  54. Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)
  55.  
  56. iCol = cStartColumn
  57. iRow = cStartRow
  58.  
  59.  
  60.     If Not rst.BOF Then
  61.         rst.MoveFirst
  62.             Do Until rst.EOF
  63.                 iFld = 0
  64.                 lRecords = lRecords + 1
  65.                 Me.Label16.Caption = "Exporting record #" & lRecords & " to ContractsFormTest.xls"
  66.                 Me.Repaint
  67.  
  68.  
  69.             rst.MoveNext
  70.         Loop
  71.  
  72.  
  73.  
  74.     ExportQuery = "Total of " & lRecords & "rows processed."
  75.  
  76.  
  77. exit_Here:
  78.  
  79. Set wbk = Nothing
  80. appExcel.Quit
  81. Set appExcel = Nothing
  82. Set rst = Nothing
  83. Set dbs = Nothing
  84. DoCmd.Hourglass False
  85. Exit Function
  86.  
  87. err_Handler:
  88. ExportQuery = Err.Description
  89. Resume exit_Here
  90. End If
  91.  
  92. End Function
  93.  
Nov 20 '07 #1
Share this Question
Share on Google+
1 Reply


Rabbit
Expert Mod 10K+
P: 12,359
Please use code tags.

You have not told us:
1) What the error message was.
2) What the SQL error message was.

You have:
1) Dimmed worksheets yet did not use them.
2) Open workbooks and yet used the automated export function.
Nov 26 '07 #2

Post your reply

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