I have a student grades database (A2010) where I want to export a grades table to an Excel workbook where the export creates a tab for the day and exports the data to that specific date tab. Where I'm struggling with code is testing to see if the date already exists, overwrite the data; if the date does not exist, create a new tab and export data. I also want to set column widths and turn on filtering to make the spreadsheet more user friendly. Here is the code I've pieced together from the internet:
Public Function ExportGrades()
Dim strTab As String
Dim strFileName As String
Dim strWkSt As String
Dim strWkbkName As String
Dim objXL As Object
strFileName = Application.CurrentProject.Path & "\CurrentQtrGrades.xls"
strTab = Format(Now, "mmdd")
'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblCurrentQtrGrades", _
' strFileName, True, strTab
DoCmd.TransferSpreadsheet transfertype:=acExport, _
strWkbkName = strFileName
strWkSt = strTab
Set objXL = CreateObject("Excel.Application")
.Rows("1:1").Font.Bold = True
I am running into problems with the modifications of the Excel file as this code has to open the file - which appears to be creating some problems.
Thanks in advance.