Excel Automation Select Range Only with Data

[orcinus]

Have some Access code using Excel Automation that pastes data from a query into Excel. I want to then be able to select all data that is in column F..starting with F1..

The data in column F will vary, but will be contiguous. So, at any given run of the code, column F could be populated from F1:F5 or at another time F1:F10 etc..

Once selected, I want to shade the range..

Any Access code automation help for this is appreciated..!

[ajetrumpet]

record a macro in excel and do just what you said.

then, in access, add that code after your "application object." section of the code. it works the exact same way.

for your info, ranges can be referenced from top to bottom in one column or row, IF they have no blank cells in the middle of the range, like this:

range("a1", range("a1").end(xldown))

and for rows of course, it changes to "xltoright" (or something like that!)

[orcinus]

record a macro in excel and do just what you said.

then, in access, add that code after your "application object." section of the code. it works the exact same way.

for your info, ranges can be referenced from top to bottom in one column or row, IF they have no blank cells in the middle of the range, like this:

range("a1", range("a1").end(xldown))

and for rows of course, it changes to "xltoright" (or something like that!)

ok, so i added the part of the code that you suggested (bolded below) and tried to run just up to that part without setting the shading yet. However, I get an error when it tries to process that part of the code:

Method 'range of object' _Global failed

Note: I added the select at the end as Access wouldn't allow the code without adding something at the end (got compile "expected = " error)


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("D1:D1").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

[ajetrumpet]

i don't know what to tell you bud. my trial found this to be just fine in exceL

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

no error.

you know, if you have the EXCEL referenced checked in the vba references, you don't have to use early binding like that, and use the createobject() method. or wait...that's late binding, right?

whatever...anyway, why not try it the other way? honestly, I don't know you get the error, especially since the ".range" part of the line works on the line right above, which is "copyfromrecordset".

so I would try to use the excel ref and see if the intellisense for ".select" appears. If it doesn't you know you have a reference problem or something.

I'm sorry I can't much at this point. I don't do much automation between xls and mdb right now.