[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1318
  • Last Modified:

formatting an excel spreadsheet using vb code in access

Dear Experts,

I'm trying to send query results from Access to an Excel spreadsheet and format the spreadsheet using VB code in an Access module.  I managed to copy some code from a Lotus Notes application.  Some of it works and some not....  I'm not really sure I understand all of the parameters needed.

I'd like to do the following:
- set column widths for some columns
- wrap text for some columns
- format dates
- create header and footer
- set page orientation
- set page margins

I have been able to set the page orientaion, create the header and footer and bold the column heading - but am stuck on the rest....and have no reference guide.

Below is the code that is working.

Thanks for your help,
je

    Set xlApp = CreateObject("Excel.Application")
    xlApp.StatusBar = "Creating WorkSheet...."
    xlApp.Visible = True
    exportname = "qry_CCM_Priority_Report"
    DoCmd.OpenQuery exportname
    DoCmd.RunCommand acCmdOutputToExcel
   
    xlApp.StatusBar = "Formatting Cells...."
    xlApp.rows("1:1").Select
    xlApp.Selection.Font.Bold = True

     xlApp.Selection.Font.Name = "Arial"
    xlApp.Selection.Font.Size = 9
    xlApp.ReferenceStyle = 3
   
    xlApp.Worksheets(1).PageSetup.Orientation = 2
    xlApp.Worksheets(1).PageSetup.CenterHeader = "CCM Priority Report"
    xlApp.Worksheets(1).PageSetup.RightFooter = "Page &P" & Chr$(13) & "Date: &D"
    xlApp.Worksheets(1).PageSetup.CenterFooter = ""
0
aeolianje
Asked:
aeolianje
  • 6
  • 5
1 Solution
 
ahammarCommented:
Just have Access create the Excel file for you, then open it with an instance of Excel.  Try the code below...change lines 8 and 9 to suit and read the comments so you can pick and choose what you want to do.  There are 2 lines you have to comment out or delete anyway, I just put them in as examples of what you can do once the sheet is created...you will see it towards the bottom in the comments

Make sure you have the "Microsoft Excel 10 (or later) object library" added in >Tools>References

:-)
Albert



Sub CreateFile()
dim stDocName as string
dim PathFile as string
Dim Exc As Excel.Application
Dim wb As Workbook
Dim sh As Worksheet
 
PathFile = "C:\" 'Path where you want the spreadsheet created
stDocName = "YourQueryNameHere" 'The query you want to trasfer to a spreadsheet
 
'Have Access create the spreadsheet
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
    stDocName, PathFile, True
 
'Set Exc to an Excel application
'Set wb to the workbook that was created by TransferSpreadsheet above
'and set sh to the first sheet
        Set Exc = New Excel.Application
        Set wb = Exc.Workbooks.Open(PathFile) 'Open the xls file created by Access
            Set sh = wb.Sheets(1)
Exc.Visible = True 'Optional only if you want to see it
 
'Now you can do all kinds of things with sh  (the first sheet in the workbook
'For example: (Comment these next 2 lines before running this macro)
sh.range("A1") = "Hello" 'Makes A1 = Hello
sh.columns("A:A").ColumnWidth = 18 'set column A width to 18
'You can record macros in Excel to get the proper formatting commands
 
wb.Save 'Save the workbook
 
    wb.Close False 'Close the workbook without saving it (it was saved above)
    Exc.Quit 'Close Excel
 
'Set variables to nothing    
wb = Nothing
Set Exc = Nothing
 
End Sub

Open in new window

0
 
aeolianjeAuthor Commented:
Albert,

Thanks for the info....

'Now you can do all kinds of things with sh  (the first sheet in the workbook
>> the thing is -- I don't know what the proper syntax is for the 'other things'  Can you direct me to a reference?

Thanks,
je
0
 
ahammarCommented:
The best way is to just open Excel and start the macro recorder.  Then just do what you want done...stop the recorder, then view the code.  I would suggest doing only 1 or 2 things at a time though, and make many small macros, instead of 1 large one, so each code segment is short and it will be much easier to figure out what code did what.  The recorded macro will not use the sh sheet object so you will have to add that.  For example, If you record a macro and select D4 and type in Hello, then hit enter, then stop the recorder...this is what it will record:
    Range("D4").Select
    ActiveCell.FormulaR1C1 = "'Hello"
    Range("E4").Select

You will have to add the sh in front of that and use this:
    sh.Range("D4").Select
    sh.ActiveCell.FormulaR1C1 = "'Hello"
    sh.Range("E4").Select

The recorder will not be the most effecient way of doing things, but it will work.  For example, the above recorded sample would be much better if it was replaced with this:
sh.Range("D4").Value = "Hello"

But here are some examples to do some of the things you listed using sh in the code above:
To change the width of column B to 18:
  sh.Columns("B:B").ColumnWidth = 18

To wrap text in columns A,  E, G, I, and K
sh.Range("A:A,E:E,G:G,I:I,K:K").WrapText = True

I will be back with more later or tomorrow...something just came up and I gotta run...sorry...but I'll be back with more..

:-)
Albert
0
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.

 
ahammarCommented:
How you coming along?...sorry I had to leave like that.

Heres a few more examples

To format C1 to have a date format of: "mm/dd/yy"
    sh.range("C1").NumberFormat = "mm/dd/yy;@"

To create a custom Center header that says "Header 1":
    sh.PageSetup.CenterHeader = "Header 1"

To create a custom Center footer that says "Footer 1":
     sh.PageSetup.CenterFooter = "Footer1"

To set page orientation to Portrait :
     sh.pageSetup.Orientation = xlPortrait 'Change to xlLandScape for landscape

To set left margin to 1.25
    sh.PageSetup.LeftMargin = Application.InchesToPoints(1.25)
(You can do the same with RightMargin, TopMargin, and BottomMargin

Is this working for you so far?  Do you have any more questions about this?

:-)
Albert


0
 
aeolianjeAuthor Commented:
Albert,

Thanks for taking the time to help me out...

I manage to get some to work and not others.  Here's what I have so far.

    Set xlApp = CreateObject("Excel.Application")
    xlApp.StatusBar = "Creating WorkSheet...."
    xlApp.Visible = True
    exportname = "qry_CCM_Priority_Report"
    DoCmd.OpenQuery exportname
    DoCmd.RunCommand acCmdOutputToExcel

Working:    
    xlApp.StatusBar = "Formatting Cells...."
    xlApp.rows("1:1").Select
    xlApp.Selection.Font.Bold = True
    xlApp.Selection.Font.Name = "Arial"
    xlApp.Selection.Font.Size = 9
    'xlApp.ReferenceStyle = 3
    xlApp.Worksheets(1).PageSetup.CenterHeader = "CCM Priority Report"
    xlApp.Worksheets(1).PageSetup.RightFooter = "Page &P" & Chr$(13) & "Date: &D"
    xlApp.Worksheets(1).PageSetup.CenterFooter = ""
    xlApp.Worksheets(1).Name = "CCI Priority Report"
    xlApp.Worksheets(1).Columns("G:G").ColumnWidth = 50
    xlApp.Worksheets(1).Columns("G:G").WrapText = True
    xlApp.Worksheets(1).PageSetup.Orientation = xlLandscape
 
Not Working:
    xlApp.Worksheets(1).PageSetup.LeftMargin = Application.InchesToPoints(0.25)
>> I get an error message when compiling: "Method or data member not found"    

I'd like to:
- set column width, wrap text and see all the text (autofit seems to put all columns in the same width)
- I'd like to show gid lines

I've seen some other solutions that include 'With select" -- but this doesn't seem to work for me.

Do you think I'm missing a reference?  I've attached my references.  Is there anyplace that I can read up on this more...?

Thanks,
je
References.pdf
0
 
aeolianjeAuthor Commented:
Albert....

I'm getting the hang of it.  Thanks for all your suggestions.  I think I can get what I need by trial and error.  I've managed to get most of the formatting that I wanted.  Don't know why the InchesToPoints or the With doesn't work.  I used a regular number for the margins and qualified the selections and they work.  Below is what I've gotten to work so far - Thanks again for your help.  I think I can close this question out now - je
   
    Set xlApp = CreateObject("Excel.Application")
    xlApp.StatusBar = "Creating WorkSheet...."
    xlApp.Visible = True
    exportname = "qry_CCM_Priority_Report"
    DoCmd.OpenQuery exportname
    DoCmd.RunCommand acCmdOutputToExcel
   
    xlApp.StatusBar = "Formatting Cells...."
    xlApp.rows("1:1").Select
    xlApp.Selection.Font.Bold = True
    xlApp.Selection.Font.Name = "Arial"
    xlApp.Selection.Font.Size = 9
    'xlApp.ReferenceStyle = 3
   
    xlApp.Worksheets(1).PageSetup.Orientation = xlLandscape
    xlApp.Worksheets(1).PageSetup.PrintTitleRows = "$1:$1"
    xlApp.Worksheets(1).PageSetup.PrintTitleColumns = ""
    xlApp.Worksheets(1).PageSetup.FitToPagesWide = 1
    xlApp.Worksheets(1).PageSetup.Zoom = 75

    xlApp.Worksheets(1).PageSetup.CenterHeader = "CCM Priority Report"
    xlApp.Worksheets(1).PageSetup.RightFooter = "Page &P" & Chr$(13) & "Date: &D"
    xlApp.Worksheets(1).PageSetup.CenterFooter = ""
    xlApp.Worksheets(1).Name = "CCI Priority Report"
    xlApp.ActiveWindow.Zoom = 75

    xlApp.Worksheets(1).PageSetup.LeftMargin = "18"
    xlApp.Worksheets(1).PageSetup.RightMargin = "18"
    xlApp.Worksheets(1).PageSetup.TopMargin = "36"
    xlApp.Worksheets(1).PageSetup.BottomMargin = "36"
    xlApp.Worksheets(1).PageSetup.HeaderMargin = "18"
    xlApp.Worksheets(1).PageSetup.FooterMargin = "18"

    xlApp.Worksheets(1).Columns("G:G").ColumnWidth = 50
    xlApp.Worksheets(1).Columns("G:G").RowHeight = 50
    xlApp.Worksheets(1).Columns("G:G").WrapText = True
    xlApp.Worksheets(1).Columns("C:F").Select
    xlApp.Worksheets(1).Columns("C:F").EntireColumn.AutoFit
    xlApp.Worksheets(1).Cells.Select
    xlApp.Worksheets(1).Cells.EntireRow.AutoFit
    xlApp.Worksheets(1).Columns("D:F").Select
    xlApp.Worksheets(1).Columns("D:F").EntireColumn.NumberFormat = "mm/dd/yy;@"

0
 
aeolianjeAuthor Commented:
Thanks for all the help!
0
 
ahammarCommented:
Thanks for the points and the grade!  I was just finishing up a lengthy comment when you posted, but now I'll write this one instead...lol..
I'm glad you got it working.  All I'm gonna do now then is just redo what you just posted using "With" and post it.  Row 1 will not be selected though.  It's better not to select things unless you have a reason to...but in case you are interested, here is the same code you just posted revised using "With".

:-)
Albert



Set xlApp = CreateObject("Excel.Application")
    xlApp.StatusBar = "Creating WorkSheet...."
    xlApp.Visible = True
    exportname = "qry_CCM_Priority_Report"
    DoCmd.OpenQuery exportname
    DoCmd.RunCommand acCmdOutputToExcel
    
    xlApp.StatusBar = "Formatting Cells...."
    With xlApp.Rows("1:1")
        .Font.Bold = True
        .Font.Name = "Arial"
        .Font.Size = 9
    End With
    'xlApp.ReferenceStyle = 3
    
    With xlApp.Worksheets(1)
        .PageSetup.Orientation = xlLandscape
        .PageSetup.PrintTitleRows = "$1:$1"
        .PageSetup.PrintTitleColumns = ""
        .PageSetup.FitToPagesWide = 1
        .PageSetup.Zoom = 75
    
        .PageSetup.CenterHeader = "CCM Priority Report"
        .PageSetup.RightFooter = "Page &P" & Chr$(13) & "Date: &D"
        .PageSetup.CenterFooter = ""
        .Name = "CCI Priority Report"
    xlApp.ActiveWindow.Zoom = 75
 
        .PageSetup.LeftMargin = "18"
        .PageSetup.RightMargin = "18"
        .PageSetup.TopMargin = "36"
        .PageSetup.BottomMargin = "36"
        .PageSetup.HeaderMargin = "18"
        .PageSetup.FooterMargin = "18"
 
        .Columns("G:G").ColumnWidth = 50
        .Columns("G:G").RowHeight = 50
        .Columns("G:G").WrapText = True
        .Columns("C:F").Select
        .Columns("C:F").EntireColumn.AutoFit
        .Cells.Select
        .Cells.EntireRow.AutoFit
        .Columns("D:F").Select
        .Columns("D:F").EntireColumn.NumberFormat = "mm/dd/yy;@"
    End With

Open in new window

0
 
ahammarCommented:
I just noticed something: These lines:
xlApp.rows("1:1").Select
    xlApp.Selection.Font.Bold = True
    xlApp.Selection.Font.Name = "Arial"
    xlApp.Selection.Font.Size = 9


should not work...are you sure they are?
0
 
aeolianjeAuthor Commented:
Albert,

Yes -- these lines work -- however, the "With" does not, that's why I end up writing them out sepearatly.  Any ideas? -- Thanks, je

I just noticed something: These lines:
xlApp.rows("1:1").Select
    xlApp.Selection.Font.Bold = True
    xlApp.Selection.Font.Name = "Arial"
    xlApp.Selection.Font.Size = 9
0
 
aeolianjeAuthor Commented:
I take that back -- I cut and pasted your code into a new module and it worked!!!

Thanks - it looks much neater and easier to understand.....  

Have a great weekend -- and thanks so much for your help!

je
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now