Solved

Formatting Excel spreadsheet via Access VBA

Posted on 2011-03-16
3
433 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:Dale Fye (Access MVP)
  • 2
3 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35153316
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 35153322

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
 
LVL 47

Author Closing Comment

by:Dale Fye (Access MVP)
ID: 35153503
Exactly what I was looking for.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question