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. -
Private Function ExportQuery() As String
-
On Error GoTo err_Handler
-
-
-
-
Dim appExcel As Excel.Application
-
Dim wbk As Excel.Workbook
-
Dim wks As Excel.Worksheet
-
Dim wks2 As Excel.Worksheet
-
-
Dim sTemplate As String
-
Dim sTempFile As String
-
Dim sOutput As String
-
-
Dim dbs As DAO.Database
-
Dim rst As DAO.Recordset
-
Dim sSQL As String
-
Dim lRecords As Long
-
Dim iRow As Integer
-
Dim iCol As Integer
-
Dim iFld As Integer
-
-
Const cTabOne As Byte = 1
-
Const cTabTwo As Byte = 2
-
Const cStartRow As Byte = 3
-
Const cStartColumn As Byte = 1
-
-
DoCmd.Hourglass True
-
Excel.Application.SetOption "Error Tapping", 0
-
-
sTemplate = "J:\2007\Databases\Contracts\ContractsForm.xls"
-
sOutput = "J:\2007\Databases\Contracts\ContractsFormTest.xls"
-
-
If Dir(sOutput) <> "" Then Kill sOutput
-
-
FileCopy sTemplate, sOutput
-
-
Set appExcel = New Excel.Application
-
appExcel.Visible = True
-
Set wbk = appExcel.Workbooks.Open(sOutput)
-
-
'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]))"
-
sSQL = "SELECT * FROM tblDashboard"
-
-
'DoCmd.OpenQuery "qryContracts", acViewDesign, acEdit
-
'DoCmd.OutputTo acOutputQuery, "", "MicrosoftExcel(.xls)", "ContractsForm", True
-
'DoCmd.SetWarnings False
-
'DoCmd.Close acQuery, "qryContracts", acSaveYes
-
-
-
-
-
Set dbs = CurrentDb
-
Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)
-
-
iCol = cStartColumn
-
iRow = cStartRow
-
-
-
If Not rst.BOF Then
-
rst.MoveFirst
-
Do Until rst.EOF
-
iFld = 0
-
lRecords = lRecords + 1
-
Me.Label16.Caption = "Exporting record #" & lRecords & " to ContractsFormTest.xls"
-
Me.Repaint
-
-
-
rst.MoveNext
-
Loop
-
-
-
-
ExportQuery = "Total of " & lRecords & "rows processed."
-
-
-
exit_Here:
-
-
Set wbk = Nothing
-
appExcel.Quit
-
Set appExcel = Nothing
-
Set rst = Nothing
-
Set dbs = Nothing
-
DoCmd.Hourglass False
-
Exit Function
-
-
err_Handler:
-
ExportQuery = Err.Description
-
Resume exit_Here
-
End If
-
-
End Function
-
1 2721
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.
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: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |