472,364 Members | 1,595 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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

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
  6. Dim appExcel As Excel.Application
  7. Dim wbk As Excel.Workbook
  8. Dim wks As Excel.Worksheet
  9. Dim wks2 As Excel.Worksheet
  11. Dim sTemplate As String
  12. Dim sTempFile As String
  13. Dim sOutput As String
  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
  23. Const cTabOne As Byte = 1
  24. Const cTabTwo As Byte = 2
  25. Const cStartRow As Byte = 3
  26. Const cStartColumn As Byte = 1
  28. DoCmd.Hourglass True
  29. Excel.Application.SetOption "Error Tapping", 0
  31.     sTemplate = "J:\2007\Databases\Contracts\ContractsForm.xls"
  32.     sOutput = "J:\2007\Databases\Contracts\ContractsFormTest.xls"
  34. If Dir(sOutput) <> "" Then Kill sOutput
  36. FileCopy sTemplate, sOutput
  38. Set appExcel = New Excel.Application
  39. appExcel.Visible = True
  40. Set wbk = appExcel.Workbooks.Open(sOutput)
  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"
  45. 'DoCmd.OpenQuery "qryContracts", acViewDesign, acEdit
  46. 'DoCmd.OutputTo acOutputQuery, "", "MicrosoftExcel(.xls)", "ContractsForm", True
  47. 'DoCmd.SetWarnings False
  48. 'DoCmd.Close acQuery, "qryContracts", acSaveYes
  53. Set dbs = CurrentDb
  54. Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)
  56. iCol = cStartColumn
  57. iRow = cStartRow
  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
  69.             rst.MoveNext
  70.         Loop
  74.     ExportQuery = "Total of " & lRecords & "rows processed."
  77. exit_Here:
  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
  87. err_Handler:
  88. ExportQuery = Err.Description
  89. Resume exit_Here
  90. End If
  92. End Function
Nov 20 '07 #1
1 2648
12,516 Expert Mod 8TB
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

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

Similar topics

by: Chris | last post by:
Could someone please provide me an effective means of exporting data from a data set (or data grid) to Excel?
by: sifar | last post by:
Hi, I am right now learning PHP & want to know if there is a way to send web form data to an Excel sheet located on a network. My windows xp PC doesnot have a copy of Excel. Also i am not...
by: Mr. Bungle | last post by:
When importing excel from access I am fully aware that one can import directly into a table. Can you get as specific via code or something to import data from an excel sheet to a FORM (Not Table)...
by: Peter | last post by:
Is there a fast way to move data from DataTable into double array, or do I have to spin through every record and column? I have five tables and I need to merge these tables column wise, each table...
by: John Machin | last post by:
I am pleased to announce a new general release (0.5.2) of xlrd, a Python package for extracting data from Microsoft Excel spreadsheets. CHANGES: * Book and sheet objects can now be pickled and...
by: JohnM | last post by:
I can transfer from a query with DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Filenam", CPath, True I would like to use a form for the user to select and order data then...
by: Restless Native | last post by:
I am importing several Excel spreadsheets, and need to use the range in order to capture the field names. If the spreadsheets are closed, I get the following error "The table contains cells that are...
by: billa856 | last post by:
Hi I am totaly new to MS Access. I have one worksheet in excel in which I have data of company's inventory. I want to know can I transfer data from a form made in MS Access to the Excel worksheet?...
by: Khriskin | last post by:
I have searched the forums (as well as msdn and other websites) and have not been able to find a solution. Any and all suggestions would be greatly appreciated! Problem I have two Excel 2003...
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge required to effectively administer and manage Oracle...
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and credentials and received a successful connection...
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web server and have made sure to enable curl. I get a...
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the synthesis of my design into a bitstream, not the C++...
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python has gained popularity among beginners and experts...
by: Ricardo de Mila | last post by:
Dear people, good afternoon... I have a form in msAccess with lots of controls and a specific routine must be triggered if the mouse_down event happens in any control. Than I need to discover what...
by: ezappsrUS | last post by:
Hi, I wonder if someone knows where I am going wrong below. I have a continuous form and two labels where only one would be visible depending on the checkbox being checked or not. Below is the...
by: jack2019x | last post by:
hello, Is there code or static lib for hook swapchain present? I wanna hook dxgi swapchain present for dx11 and dx9.

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.