Export Access into multiple excel tabs and conditionally format

[AThom10731]

I have a MS access database and am able to export the data to a single excel file with multiple worksheets. Each worksheet is labeled with a site name. When I export the data, I would like to be able to format the data in VBA code or export to a template.

I run the code from a command button that runs the query and uses TransferSpreadsheet. Based on the values of data, I would like to fill a cell with green or red, or yellow, make the column headers bold and centered, wrap text in large columns, center a few of the columns, etc. I am not an excel expert. Please help.

Private Sub Command1_Click()
'Created a new Form called frm_CAPSummaryForm1
'and set it's RecoredSource to:
'SELECT [F_Site] FROM SEC_FindingRecords GROUP BY [F_Site];
'Added the field [F_Site] to the Detail section of the form.
'Added a Command Button (Command1) to the form with the following code:

Me.Recordset.MoveFirst
Do
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "CAP_Summary_Qry", "C:\CAPSpreadsheet.xls", True, Me.F_Site
Me.Recordset.MoveNext
Loop Until Me.Recordset.EOF
End Sub
‘My query: (CAP_Summary_Qry):
'SELECT *
'FROM SEC_FindingRecords
'WHERE ((([SEC_FindingRecords].F_Site])=[Forms]![frm_CAPSummaryForm1]![F_Site]));

 

[dhookom]

I would record a macro in Excel that performs the tasks. You can then use the code in Access with automation to update the Excel file. it's a bit advanced but it should enable what you ate looking for.

Duane
Hook'D on Access
MS Access MVP

 

[AThom10731]

Thanks for your feedback.

My first attempt to record a macro in excel earlier today failed.

I assume I should be able to generate Access VBA code to:
1) export the data to the worksheets using the code I posted earlier
2) copy and paste the excel code produced by recording the macro into the Access code
3) tweak the code in step 2 until the data in the worksheets is formatted properly.

Am I on the right track?

 

[dhookom]

Those are the steps that I would take. In the VBA editor in Access, you might want to set a reference to the Excel object library. This makes the programming easier to modify and debug.

If you have some specific questions about the code, you might want to post to the VBA Visual Basic for Applications forum.

There are lots of great resources on the web for writing VBA but Tek-Tips is going to get you quick and accurate answers from experts.

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

You are not on the right track.

1) I would COPY the SQL from your Access query and paste it into NotePad for editing if necessary.

2) In Excel 2007+ Data > Get External Data > From Access and drill down to your database. (Excel 97-2003 is similar but look for New database query)

3) In the query by example grid, open the SQL window and paste your SQL. It should work without modification, but if it does not, post your SQL here for help modifying for MS Query.

4) File > Return data to Microsoft Excel.

I would suggest using a PARAMETER query, using a cell on your sheet to either enter your selected site, or to use Data > Validation --LIST to display in in-cell drop down of your sites, that will trigger the query when that cell changes. I often use this technique.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AThom10731]

Duane,
Can you point me to a tutorial or example on the web or VBA Visual Basic for Applications forum posting that would be helpful?

 

[dhookom]

I like Skip's suggestion and assumed he would contribute more than me to this thread. I did a simple web search on "Recording Excel macro vba" and found using the macro recorder

Duane
Hook'D on Access
MS Access MVP

 

[AThom10731]

I have posted my code below. I am taking a stab at updating header in each sheet. Only the first row header in the first sheet is updated. Can you help with my bug. thanks.


Private Sub Command1_Click()
Dim site_counter As Integer
Dim SOutput As String

'If Dir(SOutput) <> "" Then Kill "C:\CAPSpreadsheet.xls"
'Created a new Form called frm_CAPSummaryForm1
'Set it's RecoredSource to:
'SELECT [F_Site] FROM SEC_FindingRecords GROUP BY [F_Site];
'Add the field [F_Site] to the Detail section.
'Add a Command Button (Command1) to the form with the following code:

Me.Recordset.MoveFirst
Do
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "CAP_Summary_Qry", "C:\CAPSpreadsheet.xls", True, Me.F_Site
site_counter = site_counter + 1
Me.Recordset.MoveNext
Loop Until Me.Recordset.EOF

Call OpenAndFormatExcel(site_counter)

End Sub
Function OpenAndFormatExcel(SheetNum As Integer)

Dim Sht As Excel.Worksheet
On Error Resume Next
Dim filePath As String


Dim xl As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

filePath = "C:\CAPSpreadsheet" & ".xls"

Set xl = CreateObject("Excel.Application")
Set xlBook = GetObject(filePath)
xl.Visible = True
xlBook.Windows(1).Visible = True

For Each x1Sheet In Worksheets
With ws
Range("A1:M1").Select
With Selection.Font
.Name = "Tahoma"
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
End With

End With
Next x1Sheet

End Function

 

[dhookom]

Rather than embarrass myself regarding Excel code, I defer to either Skip or possibly post this in the other suggested forum.

Does your code compile?

I believe I have read about replacing the ".Select" with a simple reference to the range. The ".Select" is a hangover from the macro recording.

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

Function OpenAndFormatExcel(SheetNum As Integer)
'[b][highlight]what is SheetNum doing in this function???[/highlight][/b]
     On Error Resume Next
     Dim filePath As String
     
     Dim xl As Excel.Application
     Dim xlBook As Excel.Workbook
     Dim xlSheet As Excel.Worksheet
    
    filePath = "C:\CAPSpreadsheet" & ".xls"
    
    Set xl = CreateObject("Excel.Application")
    Set xlBook = xl.Open(filePath)
    xl.Visible = True
    
    With xlBook
        .Windows(1).Visible = True
  
        For Each x1Sheet In xlBook.Worksheets
            With x1Sheet.Range("A1:M1").Font
                .Name = "Tahoma"
                .Size = 9
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Underline = xlUnderlineStyleNone
                .ColorIndex = xlAutomatic
                .TintAndShade = 0
            End With
        Next x1Sheet
'[b][highlight]are you planning to SAVE & CLOSE the workbook?[/highlight][/b]
   
        .Save
        .Close
    End With
'[b][highlight]clean up[/highlight][/b]
    xl.Quit
    
    Set xlBook = Nothing
    Set xl = Nothing
End Function

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AThom10731]

No, my code does not compile. Thanks for your suggestions.
Ann

 

[dhookom]

If it doesn't compile, please provide the error message and line causing the error.

Duane
Hook'D on Access
MS Access MVP

 

[AThom10731]

Function OpenAndFormatExcel()
Dim filePath As String

Dim xl As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

filePath = "c:\CAPSpreadsheet.xls"

Set xlBook = xl.Open(filePath)

xl.Visible = True


For Each X1Sheet In Worksheets

'Set all column headers bold and centered

X1Sheet.Range("A11").Font.Bold = True

X1Sheet.Range("A11").HorizontalAlignment = Excel.xlCenter

X1Sheet.Font.Size = 9

Next


xl.DisplayAlerts = False
xlBook.Save

'*** uncomment to keep open
'xlBook.Close True

xl.DisplayAlerts = True
x1.Save
x1.Close
x1.Quit

Set xlSheet = Nothing
Set xlBook = Nothing
Set xl = Nothing

End Function

Function I call to format the data in the Excel spreadsheet was changed. I get the exported spreadsheet with the raw data. I get a runtime error and the following line is highlighted in my code.

Set xlBook = xl.Open(filePath)


The error message is:

Run-time error 91:
Object variable or With block variable not set

 

[PHV]

The error message is self explanatory:
...
filePath = "c:\CAPSpreadsheet.xls"
[!]Set xl = CreateObject("Excel.Application")[/!]
Set xlBook = xl.Open(filePath)
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886