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

Which column is it you want to enter the data in?

And for your next lesson :)
Okay, Here are some samples.


First, here is how you open an Excel Spread*** for Automation:
'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
xlApp.DisplayAlerts = False
xlApp.Interactive = False
xlApp.ScreenUpdating = False
Set xlBook = xlApp.Workbooks.Open(varGetFileName, 0, True)
xlBook.Worksheets("Actuals_res_export").Activate
*******************
The above code uses this code. The code below should go it it's own module
just like you did for the Common Dialog API. I call mine modExcelRoutines

Option Compare Database
Option Explicit

' Declare necessary API routines:
Declare Function FindWindow Lib "user32" Alias _
"FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As Long) As Long

Declare Function SendMessage Lib "user32" Alias _
"SendMessageA" (ByVal Hwnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, _
ByVal lParam As Long) As Long

Sub DetectExcel()
' Procedure dectects a running Excel and registers it.
Const WM_USER = 1024
Dim Hwnd As Long
' If Excel is running this API call returns its handle.
Hwnd = FindWindow("XLMAIN", 0)
If Hwnd = 0 Then ' 0 means Excel not running.
Exit Sub
Else
' Excel is running so use the SendMessage API
' function to enter it in the Running Object Table.
SendMessage Hwnd, WM_USER + 18, 0, 0
End If
End Sub
***********************
Here is a formatting example
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.Interior.Color = conLightBlue
cell.NumberFormat = "##,###,##0_);[Red](##,###,##0)"
Next
Next intX
*********************************
Here is putting in formulas. You could use the Excel Sum function for your
totals
With xl***
.Cells(30, 2).Formula = "=+B29"
.Cells(30, 3).Formula = "=+B30+C29"
.Cells(30, 4).Formula = "=+C30+D29"
.Cells(30, 5).Formula = "=+D30+E29"
.Cells(30, 6).Formula = "=+E30+F29"
.Cells(30, 7).Formula = "=+F30+G29"
.Cells(30, 8).Formula = "=+G30+H29"
.Cells(30, 9).Formula = "=+H30+I29"
.Cells(30, 10).Formula = "=+I30+J29"
.Cells(30, 11).Formula = "=+J30+K29"
.Cells(30, 12).Formula = "=+K30+L29"
.Cells(30, 13).Formula = "=+L30+M29"
End With
*******************
Here's how you create a new work***
Sub CreateNew***(xlApp As Object, xlBook As Object, xl*** As Object)
'Create a new work***
xlApp.Worksheets.Add.Move after:=xlApp.Worksheets(xlApp.Worksheets.Count)
xlBook.Worksheets(xlBook.Worksheets.Count).Activate
Set xl*** = xlBook.Active***
xl***.Name = strChartName
End Sub
***************************
Then, once you are done:
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

.