I am attempting to automate the form. The code I have so far is posted
below (I edited it to only include relevant parts). The problem I am having
is collecting the information from an unbound subform that loads after the
corresponding tab on the main form is clicked. (See the asterisks below -
that's the line the code halts on.) The debug says it cannot find
Forms!frmDisposition.CSADate. Note that the subform is loaded because in
order to run this code, you have to click on the tab containing this subform
to access the command button that runs this code.
What am I doing wrong here with respect to referencing the information I
want pulled from the subform "frmDisposition?"
TIA
S. Jackson
Dim objXL As Object
Dim objActiveWkb As Object
Dim strRegion As String
Set objXL = New Excel.Application
objXL.Application.workbooks.Add Application.CurrentProject.Path &
"\SettlementTrackRpt.xls"
objXL.Visible = True
Set objActiveWkb = objXL.Application.ActiveWorkBook
With objActiveWkb
.Worksheets(1).Cells(2, 6) = Me.Surveys
.Worksheets(1).Cells(2, 7) = Me.AppealDate
.Worksheets(1).Cells(2, 8) = Me.ImposedDate
.Worksheets(1).Cells(2, 10) = Forms!frmDisposition.CSADate
*********
.Worksheets(1).Cells(2, 11) = Forms!frmDisposition.ClosedDate
.Worksheets(1).Cells(2, 13) = Me.Amt
.Worksheets(1).Cells(2, 14) = Forms!frmDisposition.CSAAmt
.Worksheets(1).Cells(2, 15) = Forms!frmDisposition.Findings
.Worksheets(1).Cells(2, 16) = Forms!frmDisposition.PymtPlan
.Worksheets(1).Cells(2, 17) = Me.T_A_C_
.Worksheets(1).Cells(2, 18) = Me.DHSAttny
.Worksheets(1).Cells(2, 19) = Me.Representative
.Worksheets(1).Cells(2, 20) = Forms!frmDisposition.SetBasis
End With
Set objActiveWkb = Nothing: Set objXL = Nothing
End Sub
Klatuu said:
There are a couple of options. If the data you are going to export to the
spreadsheet is contiguous, then you could create a range name in your
spreadsheet and use that in the TransferSpreadsheet method as the range
argument or part of the range argument if you have to idenitify the sheet.
Ignore the fact the Help says the Range doesn't work with Exports, I do it
all the time.
If the data is not contiguous, you will have to learn automation with Excel.
S Jackson said:
I want to add a button on my form [frmMaster] that will export the current
record displayed within a subform [frmDisposition] to an existing excel file
(SettlementTrackRpt.xls). Actually, I need to gather data from the main
form [frmMaster] and the subform [frmDisposition]. This data needs to go
into the 2nd row on the excel spreadsheet and will need to be placed in the
appropriate column (i.e., [frmMaster].[DHSNo] needs to go into cell A:2).
I'm lost here. I've found plenty of information about how to export an
entire object from Access to Excel, but nothing that meets my specific
needs. Can anyone point me in the right direction?
TIA
S. Jackson
