Formatting Excel spreadsheet via Access VBA

I am exporting data from Access to Excel via automation.  I use the CopyFromRecordset method to dump a bunch of data into a range of cells.

I then loop through the recordset and add the fieldnames in the 3rd row of each column.   I then want to select that cell and rotate the text by 90 degrees (clockwise).  I recorded a macro in Excel and it returned the following code.
Range("B3").Select
With Selection
   .HorizontalAlignment = xlCenter
   .VerticalAlignment = xlTop
   .WrapText = False
   .Orientation = -90
   .AddIndent = False
   .IndentLevel = 0
   .ShrinkToFit = False
   .ReadingOrder = xlContext
   .MergeCells = False
   .ColumnWidth = 5
End With

Open in new window


But I need to do this for somewhere between 30 and 75 columns (depending on data filters).  In the rest of the code, I have a column counter (intCol), which I use along with the Cells( row, col) property of the worksheet object to set and manipulate cell values.  But when I tried:

sht.cells(3, intCol).Select

instead of:

Range("B3").Select

the line of code which reads:

.Horizontal Alignment = xlCenter

generates an "Object variable or With block variable not set" error.  How should I modify this code to ensure that I can reformat the cell.
LVL 50
Dale FyeAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:

try this codes, add reference to Excel



Dim xlObj As Object, sht As Object, iCol As Integer
Dim rs As DAO.Recordset
    Set xlObj = CreateObject("Excel.Application")
    xlObj.Workbooks.Open CurrentProject.Path & "\MyExcel.xls"

    Set rs = CurrentDb.OpenRecordset("customers")
    Set sht = xlObj.activeworkbook.workSheets("Sheet1")
        For iCol = 0 To rs.Fields.Count - 1
            With sht.cells(3, iCol + 1)
                .Value = rs.Fields(iCol).Name
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlTop
                .WrapText = False
                .Orientation = -90
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .ReadingOrder = xlContext
                .MergeCells = False
                .ColumnWidth = 5
            
            End With
        Next
    sht.Range("A4").CopyFromRecordset rs  'copy the data
    xlObj.activeworkbook.Save
    
    xlObj.Quit
    Set xlObj = Nothing

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
try this codes, add reference to Excel


Dim xlObj As Object, sht As Object, iCol As Integer
Dim rs As DAO.Recordset
    Set xlObj = CreateObject("Excel.Application")
    xlObj.Workbooks.Open CurrentProject.Path & "\MyExcel.xls"

    Set rs = CurrentDb.OpenRecordset("customers")
    Set sht = xlObj.activeworkbook.workSheets("Sheet1")
        For iCol = 0 To rs.Fields.Count - 1
            With sht.cells(3, iCol + 1)
                .Value = rs.Fields(iCol).Name
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlTop
                .WrapText = False
                .Orientation = -90
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .ReadingOrder = xlContext
                .MergeCells = False
                .ColumnWidth = 5
            
            End With
        Next
    sht.Range("A4").CopyFromRecordset rs  'copy the data
    xlObj.activeworkbook.Save
    

Open in new window

   xlObj.Quit
    Set xlObj = Nothing
0
 
Dale FyeAuthor Commented:
Exactly what I was looking for.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.