Excel Automation Select Range Only with Data (1 Viewer)

[catbeasy]

I have some code that copies results of a query to excel then does some formatting. The formatting code works with the exception of one piece of the code.

The piece that doesn't work (bolded below) is supposed to highlight from cells F1 to G1 and then select down to the last populated cell (the amount of rows populated in F and G will vary but will always be contiguous. Also F and G will contain the same rows of data; so if F has 10 rows in a particular run, G will also have 1o rows)..

After this bolded code I want to apply shading to the selected cells, but I can't get passed the selection part of the code to get to adding shading to the cells.

I get this error that stops at the bolded part:

Method 'range of object' _Global failed"

Any help is appreciated..also, would help to know how to add the shading part of the code as well..Thanks!

Private Function fcnExport()
On Error GoTo Err_cmdExporttoExcel_Click
Dim automApp As Excel.Application
Dim xlWksht As Excel.Worksheet
Dim xlWkbook As Excel.Workbook
Dim rs As DAO.Recordset
Dim db As Database
Dim strSQL As String
Dim strPath As String
Dim strFP As String 'file path
Dim strFN As String 'file rpt name
Dim strDT As String 'file name date tag
Dim strFE As String 'file extention
Dim lngRecCount As Long
Dim iCols As Integer
Set db = CurrentDb
Set automApp = CreateObject("Excel.Application")
'strPath = CurrentProject.Path
strFP = "c:\6481\"
strFN = "5753_Monthly_IFP_Billing_"
strDT = Format(Date, "yyyymm")
strFE = ".xls"
strPath = strFP & strFN & strDT & strFE
strSQL = "Select * from qry_output_Metric_Final"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
With rs
.MoveLast
lngRecCount = .RecordCount
.MoveFirst
End With
With automApp
.Workbooks.Add
.DisplayAlerts = False
.Visible = True

For iCols = 0 To rs.Fields.Count - 1
.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name 'changed ".cells(2, icols + 1)" from 2 to 1
Next
.Cells.Range("A1:G1").Font.Bold = True
.Columns.Range("A:G").HorizontalAlignment = xlCenter
.Columns.Range("F1:F7").HorizontalAlignment = xlLeft
.Cells.Range("A1:A1").Interior.Color = 12632256
.Cells.Range("B1:B1").Interior.Color = 8421631
.Cells.Range("C1:C1").Interior.Color = 16776960
.Cells.Range("D11").Interior.Color = 16744576
.Cells.Range("E1:E1").Interior.Color = 8454016
.Cells.Range("F1:G1").Interior.Color = 33023
.Range("A2").CopyFromRecordset rs

.Range("F1", Range("F1").End(xlDown)).Select

.Columns.AutoFit
.ActiveWorkbook.SaveAs FileName:=strPath
End With

Exit_cmdExporttoExcel_Click:
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing
automApp.Quit
Exit Function
Err_cmdExporttoExcel_Click:
MsgBox Err.Description
Resume Exit_cmdExporttoExcel_Click

End Function

 

[Summer123]

how are u exporting to excel? and how are you calling this code to export to excel?

 

[Brianwarnock]

With identify worksheet
.Range("F1", Range("F1").End(xlDown)).Select
End With

works within Excel, I'm not experienced in running code from Access to operate in Excel and what surprised me is that the other Range references worked without reference to a worksheet, even .Cell requires that although just Cell defaults to the active sheet.

Brian

 

[catbeasy]

how are u exporting to excel? and how are you calling this code to export to excel?

This part of the code assigns variables to the excel application

Dim automApp As Excel.Application
Dim xlWksht As Excel.Worksheet
Dim xlWkbook As Excel.Workbook

then you create a recordset object from Access from your data table or query. A recordset object is a copy of your Access data table or query:

Dim rs As DAO.Recordset
Dim db As Database
Dim strSQL As String

You then specify your filepath/file name where you wish to create the excel file:

strFP = "c:\6481\"
strFN = "5753_Monthly_IFP_Billing_"
strDT = Format(Date, "yyyymm")
strFE = ".xls"
strPath = strFP & strFN & strDT & strFE

The rest of the code puts these variables into action. A recordset is created with all your table or query fields and data and output to the excel file you create using Automation. The process is similar to the send keys function in that an Excel file is created, opened and affected (like cell shading, columns widening etc) while the excel file is open..

This is a broad view of what's going on..hope it helps..