?
Solved

Formatting Excel spreadsheet via Access VBA

Posted on 2011-03-16
3
Medium Priority
?
465 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
  • 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 2000 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 49

Author Closing Comment

by:Dale Fye
ID: 35153503
Exactly what I was looking for.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

850 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