[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Formatting Excel spreadsheet via Access VBA

Posted on 2011-03-16
3
Medium Priority
?
460 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
[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 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 48

Author Closing Comment

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

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

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…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

649 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