Solved

Formatting Excel spreadsheet via Access VBA

Posted on 2011-03-16
3
443 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)
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 48

Author Closing Comment

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

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

724 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