I have an Access application that outputs data to an existing Excel Spreadsheet. I have code that writes a recordset with varying number of rows to the spreadsheet starting at Cell A25.
My existing code is below and works great.
What I need to do is:
1. Find the next blank row on the spreadsheet after the new data is written out
2. Insert some formulas in certain columns. Example: I want to sum column B and C. I want to calulate C / B in column D
so in Cell B43, I might have the formula =SUM(B25:B42) but I need to determine the the starting and ending row numbers on the fly each time.
I know the starting row is B25 everytime. I figure I need to count the rows in the record set (i.e. 17) and add to 25 to get row 42 as the last data row. How to do that?
Here's what I have so far:
Function GetExcel()
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim cell As Object
Dim blnExcelWasNotRunning As Boolean
Dim qdf As QueryDef
Dim rstItems As Recordset
Dim Mnth As String
Dim Dy As String
Dim Yr As String
Dim FileDate As String
Mnth = Month(Date)
Dy = Weekday(Date)
Yr = Year(Date)
FileDate = Mnth & Dy & Yr
On Error Resume Next
Set xlApp = New Excel.Application
With xlApp
.Visible = True
Set xlBook = .Workbooks.Open("F:\LoadedMilePercentage.xls", , False)
End With
If Err.Number <> 0 Then blnExcelWasNotRunning = True
Err.Clear
On Error GoTo Build_XL_Report_ERR
xlApp.Application.Visible = True
xlApp.Parent.Windows(1).Visible = True
xlApp.DisplayAlerts = False
xlApp.Interactive = False
xlApp.ScreenUpdating = False
'Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.ActiveSheet
'Add Data
'Get query results
Set qdf = CurrentDb.QueryDefs("qry999LoadedMilePercentage")
Set rstItems = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)
'Load Data
xlSheet.Cells(25, 1).CopyFromRecordset rstItems
rstItems.Close
Set rstItems = Nothing
Set qdf = Nothing
'Save Changes
xlBook.SaveAs Filename:="F:\LoadedMilePercentage.xls"
Build_XL_Report_Exit:
'xlBook.Close
xlApp.DisplayAlerts = True
xlApp.Interactive = True
xlApp.ScreenUpdating = True
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
DoCmd.Hourglass (False)
Exit Function
Build_XL_Report_ERR:
xlBook.Close
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
GoTo Build_XL_Report_Exit
End Function
To be honest, I'm not that slick and got this code form somewhere and it works.
Once I figure out what row to put my totals on, how do I insert the cell formulas?
In the <Load data> section of my code, can I do something like:
xlSheet.Cells(43, 2) = "=sum(B25:B42)" ?
My existing code is below and works great.
What I need to do is:
1. Find the next blank row on the spreadsheet after the new data is written out
2. Insert some formulas in certain columns. Example: I want to sum column B and C. I want to calulate C / B in column D
so in Cell B43, I might have the formula =SUM(B25:B42) but I need to determine the the starting and ending row numbers on the fly each time.
I know the starting row is B25 everytime. I figure I need to count the rows in the record set (i.e. 17) and add to 25 to get row 42 as the last data row. How to do that?
Here's what I have so far:
Function GetExcel()
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim cell As Object
Dim blnExcelWasNotRunning As Boolean
Dim qdf As QueryDef
Dim rstItems As Recordset
Dim Mnth As String
Dim Dy As String
Dim Yr As String
Dim FileDate As String
Mnth = Month(Date)
Dy = Weekday(Date)
Yr = Year(Date)
FileDate = Mnth & Dy & Yr
On Error Resume Next
Set xlApp = New Excel.Application
With xlApp
.Visible = True
Set xlBook = .Workbooks.Open("F:\LoadedMilePercentage.xls", , False)
End With
If Err.Number <> 0 Then blnExcelWasNotRunning = True
Err.Clear
On Error GoTo Build_XL_Report_ERR
xlApp.Application.Visible = True
xlApp.Parent.Windows(1).Visible = True
xlApp.DisplayAlerts = False
xlApp.Interactive = False
xlApp.ScreenUpdating = False
'Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.ActiveSheet
'Add Data
'Get query results
Set qdf = CurrentDb.QueryDefs("qry999LoadedMilePercentage")
Set rstItems = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)
'Load Data
xlSheet.Cells(25, 1).CopyFromRecordset rstItems
rstItems.Close
Set rstItems = Nothing
Set qdf = Nothing
'Save Changes
xlBook.SaveAs Filename:="F:\LoadedMilePercentage.xls"
Build_XL_Report_Exit:
'xlBook.Close
xlApp.DisplayAlerts = True
xlApp.Interactive = True
xlApp.ScreenUpdating = True
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
DoCmd.Hourglass (False)
Exit Function
Build_XL_Report_ERR:
xlBook.Close
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
GoTo Build_XL_Report_Exit
End Function
To be honest, I'm not that slick and got this code form somewhere and it works.
Once I figure out what row to put my totals on, how do I insert the cell formulas?
In the <Load data> section of my code, can I do something like:
xlSheet.Cells(43, 2) = "=sum(B25:B42)" ?
