RE: Cannot get code to work for API Save Dialog Box

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 ***:
1. The first spread*** placed where I told it to be contains the incorrect
work*** with no totals.
2. The second spread*** placed at My Documents contains the correct
work*** 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 spread*** 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 work*** to My Docs. Should not be removed, but put
to diff place
Set xlBook = xlApp.Workbooks.Open(varGetFileName, 0, True)
xl***.Name = "SupportSchedule"
xlBook.Worksheets("Support Schedule").Activate

'Activate the *** for totals
xlBook.Worksheets(1).Activate
Set xl*** = xlBook.Active***
xl***.Name = "Support Schedule Total"

' Format output
With xl***
For intX = 2 To lngItmCount + 1
strLeftRange = "C" & Trim(Str(intX))
strRightRange = "S" & Trim(Str(intX))
For Each cell In xl***.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 xl***
.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 xl*** = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

End Sub

"Klatuu" wrote:

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 Spread*** is another matter. Since the query will
rerun when you export it, whether you use the CopyFromRecordset or
TransferSpread***, 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?


"Elleve" wrote:

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.
.