While I still need help on that other issue, I fixed the totals problem I had.
The code is actually working by giving me the totals I need. But, it puts
it in a different location than I asked. The code actually creates two
spreadsheets and places them at two different locations with the same name of
the sheet:
1. The first spreadsheet placed where I told it to be contains the incorrect
worksheet with no totals.
2. The second spreadsheet placed at My Documents contains the correct
worksheet with totals.
Obviously my coding is a little bit twisted and I need some help in figuring
out how to make it save the correct spreadsheet only under the correct place.
Here is my code:
Private Sub cmdExportSupportSchedule_Click()
Dim strFilter As String
Dim lngFlags As Long
Dim strDefaultDir As String
Dim varGetFileName As Variant
'Set filter to show only Excel spreadsheets
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)")
'Hides the Read Only Check Box on the Dialog box
lngFlags = ahtOFN_HIDEREADONLY
'Get the File Name To Save
strDefaultDir = "c:\"
varGetFileName = ahtCommonFileOpenSave( _
OpenFile:=False, _
InitialDir:=strDefaultDir, _
Filter:=strFilter, _
FileName:=strDefaultFileName, _
Flags:=lngFlags, _
DialogTitle:="Save Report")
Me.Repaint
If varGetFileName <> "" Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qrySupportScheduleUnionqry1and2", varGetFileName, True
End If
'Open Excel
On Error Resume Next ' Defer error trapping.
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
blnExcelWasNotRunning = True
Set xlApp = CreateObject("excel.application")
Else
DetectExcel
End If
Err.Clear ' Clear Err object in case error occurred.
'On Error GoTo LoadAdjustedActuals_Err
DoEvents
'Puts the correct worksheet to My Docs. Should not be removed, but put
to diff place
Set xlBook = xlApp.Workbooks.Open(varGetFileName, 0, True)
xlSheet.Name = "SupportSchedule"
xlBook.Worksheets("Support Schedule").Activate
'Activate the sheet for totals
xlBook.Worksheets(1).Activate
Set xlSheet = xlBook.ActiveSheet
xlSheet.Name = "Support Schedule Total"
' Format output
With xlSheet
For intX = 2 To lngItmCount + 1
strLeftRange = "C" & Trim(Str(intX))
strRightRange = "S" & Trim(Str(intX))
For Each cell In xlSheet.Range(strLeftRange, strRightRange)
cell.Font.Size = 10
cell.Font.Name = "Arial"
cell.Font.Bold = True
cell.NumberFormat = "##,###,##0_);[Red](##,###,##0)"
Next
Next intX
End With
'Formulas
With xlSheet
.Cells(25, 6).formula = "=sum(F2:F24)"
.Cells(25, 7).formula = "=sum(G2:G24)"
.Cells(25, 8).formula = "=sum(H2:H24)"
.Cells(25, 9).formula = "=sum(I2:I24)"
End With
'Done and save
xlBook.Save
xlBook.Close
If blnExcelWasNotRunning = True Then
xlApp.Quit
Else
xlApp.DisplayAlerts = True
xlApp.Interactive = True
xlApp.ScreenUpdating = True
End If
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
End Sub
Klatuu said:
If the record source for your form is a table, you will need to add the field
to the table and put a control on your form bound to the field. If it is a
query, you will have to add the field to your query.
If the field will not be stored in a table, but only used in the query, you
can add a field to a query that does not exist in any table by giving it a
name and an intial value in your query. For example, if your EndingBalance
is based on the sum of field one and field two, then in your query builder
where you would put a table field,
BalanceEnding: [fieldone] + [fieldtwo]
If you want to create a field that has no initial value, you ca do the same
thing with whatever value you need to match the data that will be entered.
To start with zero:
SomeField: 0
To start with Null
SomeField: Null
To start with an empty string:
SomeField: ""
Now, to get it to the Spreadsheet is another matter. Since the query will
rerun when you export it, whether you use the CopyFromRecordset or
TransferSpreadsheet, the values entered will be lost unless saved in a table.
You could create a table that is the image you want to send to Excel. Then
for your form you would need to use a query as the recordset that would
include those fields. The you could use the table as the record source for
your export.
Have I managed to thoroughly confuse you yet?
:
Here is some information I believe you might find useful. There are total
four fields to be shown in my form.
1. Field one called 'Beginning Balance' (created in qry, based on total)
2. Field two called 'Activity' (from table)
3. Field three called 'Ending Balance' (calculated sum of Field one and
Field two)
4. Field four called 'Approved' (created field where user can edit
information)
The information used for this comes from the same table under one field
'Activity'. The table contains entries for the entire year and I select the
months I want in my queries. However, the amount calculated under 'Beginning
Balance' is based on a running sum of prior months of 'activity'. This
works perfect. Then I have a union query that is based on two other queries.
Not sure if this affects anything, but I thought it might be helpful.