Link to home
Start Free TrialLog in
Avatar of Heartless91
Heartless91Flag for United States of America

asked on

Access Table to Excel with formatting

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, _
    spreadsheettype:=acSpreadsheetTypeExcel9, _
    TableName:="tblCurrentQtrGrades", _
    FileName:=strFileName, _
    hasfieldnames:=True, _
    range:=strTab
    
strWkbkName = strFileName
    
     strWkSt = strTab
    Set objXL = CreateObject("Excel.Application")
    objXL.Workbooks.Open (strWkbkName)
    With objXL
        .Worksheets(strWkSt) _
        .Rows("1:1").Font.Bold = True
        .Columns("A:Z").Autofit
        .Cells.Rows(1).AutoFilter
        .Save
        .Workbooks.Close
    End With
End Function

Open in new window


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.
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

which appears to be creating some problems

Like what, specifically?
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Heartless91

ASKER

Receiving an error:

Run-time error '438':
Object doesn't support this property or method

Referencing
 if objXl.worsheets(j).name=strTab then
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I caught the typo just a few moments ago. The code runs without error but when I open the Excel file, it opens and closes. It shows as a process in Windows Task Manager. Once I quit the process, then I can open the file. I can see that it did the overwrite but it did not turn on the autofiltering.

Hmmmm. Ran it again, autofiltering worked but autofit didn't...
<It shows as a process in Windows Task Manager. > 
that could have been caused by the previous error.


<but it did not turn on the autofiltering. > are you sure
this line should do it
 
             .Selection.AutoFilter


try running again the codes
Ran again with everything working but the autofit.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Modified code per your instructions. Everything works... I think.

The code hung this morning due to some typos. Once that was corrected, it ran but since the sheet already existed, it did not execute the autofit or the autofilter. I deleted the sheet and reran the code and everything worked.

As I see it, two problems still exist:
1) The first time the Excel file is opened after running, the entire sheet is selected. I can see the potential for a user to inadvertently delete the data. I added the following code after the  Autofit code. It looks like:
            .Selection.Columns.AutoFit
            .Range("A1:A1").Select

2) The second problem I hadn't considered and I understand if this is a separate issue. I didn't consider what would happen if a user deleted or moved the file. I recognize the need to check for the file before trying to open it - or - will it automatically create the file if it doesn't exist?

Thanks again. You have already been a tremendous help.
Modified code per your instructions. Everything works... I think.

The code hung this morning due to some typos. Once that was corrected, it ran but since the sheet already existed, it did not execute the autofit or the autofilter. I deleted the sheet and reran the code and everything worked.

As I see it, two problems still exist:
1) The first time the Excel file is opened after running, the entire sheet is selected. I can see the potential for a user to inadvertently delete the data. I added the following code after the  Autofit code. It looks like:
            .Selection.Columns.AutoFit
            .Range("A1:A1").Select                        'This is the line I added

2) The second problem I hadn't considered and I understand if this is a separate issue. I didn't consider what would happen if a user deleted or moved the file. I recognize the need to check for the file before trying to open it - or - will it automatically create the file if it doesn't exist?

Thanks again. You have already been a tremendous help.
< I recognize the need to check for the file before trying to open it - or - will it automatically create the file if it doesn't exist?>

with the current codes, it will raise an error if the file does not exists.
Great help! Thanks.