I've got my query exported, now I'd like to format it.

[catluvr]

Now that I can export to a single Excel file with info on separate tabs, I'd like to format the data. Specifically, I'd like to set the columns to autofit to the data and the first row to be bold.



I found code that will allow me to do that and I've adjusted it to have the result I want, except it's for a specific workbook and a specific tab title.

I want the newly created Excel file (whose file name will change based on the date) to be used and the formatting to apply to all the sheets in the file, even when I don't know their names or how many there are.

I did find a reference to a For Each/Next sequence that I thought worked, but when I ran it again, I'm getting an error.

Sub TestExcelFormatting()

Dim XLapp As Excel.Application
Dim xlWB As Excel.Workbook
Set XLapp = New Excel.Application 
Dim xlSh As Excel.Worksheet   'not used
Dim xlObj As Object         'not used
Dim wkSht As Excel.Worksheet
Dim NewFileName As String       'full Excel file name, including dir path, without extension
Dim xlName As String            'Full Excel name, including extension


NewFileName = "C:\Users\XXXXX\Documents\DayCamp 2018\Copy of 2018DayCampRoster04-07"   'this is a test workbook. When this code is working, it will be at the end of the sub that creates this file.
xlName = NewFileName & ".xlsx"    'this is needed b/c the NewFileName is created in an earlier portion of the sub
Debug.Print xlName    'so I know the code has started
Set xlWB = XLapp.Workbooks.Open(xlName)
    For Each wkSht In activeworkbook.Worksheets
        'wkSht.Cells.EntireColumn.AutoFit
        'wkSht.Range("1:1").Font.Bold = True
        MsgBox wkSht.Name
    Next wkSht


xlWB.Close True
Set xlWB = Nothing
XLapp.Quit
Set XLapp = Nothing


End Sub

I've commented out the processes I really want ... the AutoFit and the Bold and added a message box ... I wanted to see that the For Each/Next was working.

The code stops and highlights the line that says "For Each wkSht In activeworkbook.Worksheets" and the error is: Run-time error 91: Object variable or With block variable not set.

So, several questions:

1. What am I doing wrong?

2. When I run this code, the file gets placed in an open state. Since the code doesn't finish, the file is not "released." Is there a way to do that? I have another sub that simply contains Excel.Application.Quit and when I run that, the file is "released." Is there another or better way? I still have one file that isn't "released" and I don't know how to get that one so I can open it and make changes.

3. I'm planing on putting this in either a completely separate sub from my export sub or at the end. I figured I should let the Excel file get all the way completed before messing around with formatting.

4. How do I "break" the code? I've gotten stuck in a seemingly endless loop a few times and the only way I've found to stop it is to shut Access down. I know Ctrl-Break doesn't work, nor does Esc. Anything else I should try?

Thank you folks, for all of your help!

Susie
Kansas

[ranman256]

you ALWAYS must reference the excel object:

    For Each wkSht In XLapp.activeworkbook.Worksheets
         wkSht.Cells.EntireColumn.AutoFit
        'wkSht.Range("1:1").Font.Bold = True
        MsgBox wkSht.Name
    Next

[catluvr]

ranman256, Thank you! That did the trick!

On to the next question (or two):

1. When I run the formatting code (see below, and this would only be the portion that autofits and bolds), and then open the spreadsheet file, all the tabs are selected together as a group. I want only one tab selected. I don't care which one. I'm concerned that the user won't realize all tabs are selected and start making changes thinking they are only making them to the first tab.


2. I have read that one way to determine what formatting code to use in the Access VBA is to make a macro in the Excel File and look at the VBA behind that ... or even copy and paste it.

I want to shade the column headings on each sheet. I don't want to shade the entire row for aesthetics ... I don't like the look of the row being shaded where there isn't data.

I made a macro to do just that. I copied the VBA to Access ... It worked one time and then hasn't worked again since then.

The error I get is Run-time error '1004’: Select method of Range class failed. The highlighted line is wkSht.Range("A1:P1").Select

When I comment out the code starting at the above line and through the End With, the error is still '1004', but the reason is: Method ‘Worksheets’ of object ‘_Global’ failed.

All the code above what's below works fine.

The DIMs are:

Dim dbs As DAO.Database
Dim xlObj As Object
Dim wkSht As Excel.Worksheet
Dim strSQL As String 'isn't used
Dim strTemp As String 'ends up as the query name
Dim strPath As String 'directory path for Excel file
Dim strDate As String 'formated date for the Excel file name
Dim NewFileName As String 'full Excel file name, incuding dir path, without extension
Dim xlName As String 'Full Excel name, including extension
Dim strUNIT_SQL As String 'ended up not using
Dim rstUnit As DAO.Recordset
Dim strUnit As String 'ended up not using
Dim strRST_SQL As String 'SQL statement to set up the recordset
Dim UnitType As String 'ended up not using
Dim strEXPORT_SQL As String 'SQL for the query
Dim sheetName As String 'sheetname will equal the unit title (ex. "Unit 2" or "ADMIN")
Dim XLapp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlSh As Excel.Worksheet 'didn't use

'format the Excel SheetsSet XLapp = New Excel.Application
xlName = NewFileName & ".xlsx"


Set xlWB = XLapp.Workbooks.Open(xlName)
    For Each wkSht In XLapp.activeworkbook.Worksheets
        wkSht.Cells.EntireColumn.AutoFit
        wkSht.Range("1:1").Font.Bold = True
        wkSht.Range("A1:P1").Select      'this is where the debugging highlights.
            With Selection.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorDark1
                .TintAndShade = -0.149998474074526
                .PatternTintAndShade = 0
            End With
        wkSht.Range("A1").Select     'I was trying to have only A1 selected when the file is finally saved. 


    Next wkSht
Worksheets(1).Activate    'I thought this would select only the first tab ... it doesn't
xlWB.Close True
Set xlWB = Nothing
XLapp.Application.Quit     'the instance of Excel wasn't closing with just this line
Set XLapp = Nothing
Excel.Application.Quit      'so I added this line and it closes.
End Sub

Again,

Many Thanks!

Susie

[ssanfu]

Excel automation is difficult at best. It takes a lot of testing to get it to work.... but it is possible.

Try this... it is a mix of my code and yours:

Option Compare Database
Option Explicit

Sub TestExcelFormatting()
    Dim XLapp As Object
    Dim xlWB As Object
    Dim xlSht As Object
    Dim Sht As Object

    Dim blnEXCEL As Boolean
    Dim xlCol As Long
    Dim xlRow As Long
    Dim i As Integer

    Dim dbs As DAO.Database
    Dim rstUnit As DAO.Recordset

    Dim NewFileName As String       'full Excel file name, including dir path, without extension
    Dim xlName As String            'Full Excel name, including extension

    Dim xlObj As Object
    Dim strTemp As String    'ends up as the query name
    Dim strPath As String    'directory path for Excel file
    Dim strDate As String    'formated date for the Excel file name
    Dim strRST_SQL As String    'SQL statement to set up the recordset
    Dim strEXPORT_SQL As String    'SQL for the query
    Dim sheetName As String    'sheetname will equal the unit title (ex. "Unit 2" or "ADMIN")

    blnEXCEL = False

    ' Establish an EXCEL application object
    On Error Resume Next
    Set XLapp = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
        Set XLapp = CreateObject("Excel.Application")
        blnEXCEL = True
    End If
    Err.Clear

    '**** Set this to false if you DO NOT want to see the workbook/worksheets
    XLapp.Visible = True
    '****

    NewFileName = "C:\Users\XXXXX\Documents\DayCamp 2018\Copy of 2018DayCampRoster04-07"   'this is a test workbook. When this code is working, it will be at the end of the sub that creates this file.
    xlName = NewFileName & ".xlsx"    'this is needed b/c the NewFileName is created in an earlier portion of the sub
    '    Debug.Print xlName    'so I know the code has started

    Set xlWB = XLapp.Workbooks.Open(xlName)

    '    Debug.Print XLapp.activeworkbook.Worksheets.Count
    For Each xlSht In XLapp.activeworkbook.Worksheets
        '    Debug.Print xlSht.Name
        xlSht.Select
        xlSht.Cells.EntireColumn.AutoFit
        xlSht.Range("1:1").Font.Bold = True
        xlSht.Range("A1:P1").Select
        With xlSht.Selection.Interior
            .Pattern = 1   'constant for xlSolid
            .PatternColorIndex = -4105   'constant for xlAutomatic
            .ThemeColor = 1   'constant for xlThemeColorDark1
            .TintAndShade = -0.149998474074526
            .PatternTintAndShade = 0
        End With
        xlSht.Range("A1").Select

    Next xlSht

    'select the first worksheet
    XLapp.activeworkbook.Worksheets(1).Select

    xlWB.Close SaveChanges:=True
    DoEvents   '<<<-- I found this is needed to give windows time to save the file....
    Set xlWB = Nothing
    If blnEXCEL = True Then
        XLapp.Quit
    End If
    Set XLapp = Nothing

    MsgBox "Done"

End Sub

[isladogs]

Have you considered exporting to a pre formatted Excel template file?

[trevor40]

Hi,
I have posted lots of code on how to drive Excel from Access, search on my user name for examples of just about everything you can do in Excel by using Access VBA, including formatting, module creation, Excel VBA code, macro's for inserted buttons, conditional formatting, adding/formatting tables, formula inserting, print setup, multi page setup and editing. to name just a few...

regards
Trevor.

[catluvr]

Thank you!

But, it never ends, huh? The font being bold and the first sheet only being selected worked great!

However, pattern, patterncolorindex, themecolor, tintandshade and patterntintandshade don't shade the selected cells gray. There is no error. I'm not sure why, since I copied it straight from the Excel Macro.

I did some more searing and found a site that showed how to get the colorindex. I used that and it worked!

Again, Thanks to all!

Susie