Formatting several excel worksheets within one spreadsheet from Ac

[Jeff]

Sorry if I posted this on the wrong forum but I am having some difficulty
attempting the following and could use some help....

I have a query which I am exporting to several worksheets within one excel
spreadsheet using the TransferSpreadSheet command and I am wondering if there
is also a way to format the data in Excel when I send it to each worksheet...

What I would like to do is to make the "header bold text", size the columns
to "auto fit", and "center the text" in all columns.

My code for exporting the query (which is set up to dump a seperate record
from a table into it's own worsheet) is as follows:

----------------------------------------------------------------------------
DoCmd.TransferSpreadsheet acExport, cSpreadsheetTypeExcel9,
strTemp, "C:\" & strFileName & ".xls"
----------------------------------------------------------------------------

I recorded the following macro within excel on one worksheet but I am not
sure how to call this from access to format each worksheet as it is created
in my access code:

-----------------------------------------------
Sub cleanup()
'
' cleanup Macro
' This macro bolds the heading, autofits columns and centers text
'
' Keyboard Shortcut: Ctrl+z
'
Rows("1:1").Select
Selection.Font.Bold = True
Cells.Select
Selection.Columns.AutoFit
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub
-----------------------------------------------

How do I run this from access so that this formatting occurs with every
worksheet
that I create with my transferspreadsheet function?

Thanks

 

[Paolo]

Hi Jeff,

after you exported your query

Set ExcelSheet = CreateObject("Excel.Sheet")
ExcelSheet.Workbooks.Open "path_of_your_file\yourfile.xls"
ExcelSheet.Application.Rows("1:1").Select
with selection
.Font.Bold = True
end with
ExcelSheet.Application.Cells.Select
ExcelSheet.Application.Selection.Columns.AutoFit
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ExcelSheet.save

This is aircode so you have to test and debug it.

HTH Paolo

 

[Jeff]

Thanks Paolo-

When I run this I get a run time error 438
Object does not support this property or method on the
following line:


ExcelSheet.Workbooks.Open "c:\mydump.xls"

I put this code in a seperate function called by a button on a form and this
is an existing file that is not already open when I run this code.

Also, other info, I do have the Microsoft excel 11.0 Object Library
referneced and I am using Access 2003 on an XP system.

What am I doing wrong?

-------------------------

 

[Paolo]

Sorry Jeff, my mistake

instead of Set ExcelSheet = CreateObject("Excel.Sheet")
use Set ExcelSheet = CreateObject("Excel.Application")

Cheers Paolo

 

[Jeff]

Thanks Paolo-

Now I get a run time error 91
Object variable or with block variable not set on the following line:

.Font.Bold = True

 

[Jeff]

I placed this code into my loop where I dump information into a table and
export the table to excel with the transferSpreadsheet method and for some
reason it was choking something else in my code as well so I removed it...

One question remains:

I will run this macro after my spread sheet is complete but how can I
perform this macro on all worksheets withing hte spread sheet?

Thanks again

 

[Paolo]

Another correction
with selection
.Font.Bold = True
end with
become

With ExcelSheet.Application.selection
.Font.Bold = True
end with

and

With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

become
With ExcelSheet.Application.selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

 

[Socko]

I know it is possible to create a macro to get this work done.

However, if the problem is formatting multiple sheets in such a way
that they look the same, I would suggest a way to "WORK WITH MULTIPLE
SHEETS" option which is no geek thing.

Click the tab of the first worksheet that contains the data that you
want to format, then hold down CTRL while you click the tabs of other
worksheets that contain the data and you want them to be edited in a
similar mannger.

You may select several worksheets and then format the data on one of
them, if you want the changes applied to the same cells on all the
selected worksheets.

On active worksheet, select the range that contains the data you want
to format,

Now you start formating the range in the selected sheet, and remember
similar formating will be done on all the worksheets in the selection.
When you edit or format data, the changes affect all selected
worksheets.

Note: It may inadvertently replace data that you didn't mean to
change, so do make sure that you do not edit or type any characters
while you are formating. Do it carefully.

MORE ON THIS, have a look at
http://www.microsofttraining.net/post-17792-formatting-multiple-worksheets.html

I hope this helps.

Selva V Pasupathy
For more on Excel, VBA, & other resources
Please visit http://socko.wordpress.com/

 

[Jeff]

Thanks Paolo:

Not sure what I did wrong but the following gives me a compile error "Sub or
function not defined" on the "become" line:

Set ExcelSheet = CreateObject("Excel.Application")
ExcelSheet.Workbooks.Open "c:\test.xls"
ExcelSheet.Application.Rows("1:1").Select
with selection
.Font.Bold = True
end with

become
ExcelSheet.Application.Cells.Select
ExcelSheet.Application.Selection.Columns.AutoFit
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ExcelSheet.save
-------------------------------

 

[Jeff]

Thanks Socko-

Because my access code is creating the excel sheet on the fly, the
spreadsheet can be slightly different and this is why I am looking for a way
to affect all of the worksheets regardless of number of naming convention...

If I create a macro as you describe, will it work for any spreadsheet
regardless of how may tabs it has or if the tabs are named differently from
one excel file to the next?

 

[Paolo]

With become I was meaning that you have to replace this (the stuff between **)
**
with selection
.Font.Bold = True
end with
**

with this

With ExcelSheet.Application.selection
.Font.Bold = True
end with

and the same for the second amend I wrote.
All in all try in this way

Set ExcelSheet = CreateObject("Excel.Application")
ExcelSheet.Workbooks.Open "c:\test.xls"
ExcelSheet.Application.Rows("1:1").Select
With ExcelSheet.Application.selection
.Font.Bold = True
end with

ExcelSheet.Application.Cells.Select
ExcelSheet.Application.Selection.Columns.AutoFit
With ExcelSheet.Application.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Excuse me but english isn't my mother language so perhaps sometimes I don't
explain me very well...

Paolo

 

[Jeff]

Thank you Paolo-

I am not sure if I understand....

I tried the following and now get a Run-time error 424 Object required on
".Font.Bold = True line"

Set ExcelSheet = CreateObject("Excel.Application")
ExcelSheet.Workbooks.Open "c:\test.xls"
ExcelSheet.Application.Rows("1:1").Select
With Selection
.Font.Bold = True
End With

ExcelSheet.Application.Cells.Select
ExcelSheet.Application.Selection.Columns.AutoFit
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ExcelSheet.Save

thank you for your patience

-----------------------

 

[Paolo]

Copy and paste this:

Set ExcelSheet = CreateObject("Excel.Application")
ExcelSheet.Workbooks.Open "c:\test.xls"
ExcelSheet.Application.Rows("1:1").Select
With ExcelSheet.Application.Selection
.Font.Bold = True
End With

ExcelSheet.Application.Cells.Select
ExcelSheet.Application.Selection.Columns.AutoFit
With ExcelSheet.Application.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ExcelSheet.Save

Cheers Paolo

 

[Jeff]

Thank you Paolo-


I copy & pasted this and when I tried the code I got a

run-time error 1004
Unable to set the HorinzontalAlignment proprty of the Range class
on line .HorizontalAlignment = xlCenter

Do I have something set up wrong on my PC that is causing this?

 

[RoyVidar]

Sorry if I posted this on the wrong forum but I am having some
difficulty attempting the following and could use some help....

I have a query which I am exporting to several worksheets within one
excel spreadsheet using the TransferSpreadSheet command and I am
wondering if there is also a way to format the data in Excel when I
send it to each worksheet...

What I would like to do is to make the "header bold text", size the
columns to "auto fit", and "center the text" in all columns.

My code for exporting the query (which is set up to dump a seperate
record from a table into it's own worsheet) is as follows:

----------------------------------------------------------------------------
DoCmd.TransferSpreadsheet acExport, cSpreadsheetTypeExcel9,
strTemp, "C:\" & strFileName & ".xls"
----------------------------------------------------------------------------

I recorded the following macro within excel on one worksheet but I am
not sure how to call this from access to format each worksheet as it
is created in my access code:

-----------------------------------------------
Sub cleanup()
'
' cleanup Macro
' This macro bolds the heading, autofits columns and centers text
'
' Keyboard Shortcut: Ctrl+z
'
Rows("1:1").Select
Selection.Font.Bold = True
Cells.Select
Selection.Columns.AutoFit
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub
-----------------------------------------------

How do I run this from access so that this formatting occurs with
every worksheet
that I create with my transferspreadsheet function?

Thanks

Try this;

Dim xl As Object ' application
Dim wr As Object ' workbook
Dim sh As Object ' worksheet

Set xl = CreateObject("Excel.Application")
Set wr = xl.Workbooks.Open("c:\a-opg\tst2.xls")

For Each sh In wr.Worksheets
' in stead of formatting the whole row, selecting
' starting with A1 to the last cell containing value
With sh.Range("A1", sh.Range("A1").End(-4161)) ' xlToRight
.Font.Bold = True
End With

' i stead of formatting the whole sheet,
' formatting only the cells one need,
' aka currentregion

With sh.Range("A1").CurrentRegion
.Columns.AutoFit
.HorizontalAlignment = -4108 ' xlCenter
.VerticalAlignment = -4107 ' xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = -5002 ' xlContext
.MergeCells = False
End With

Next sh
Set sh = Nothing
wr.Save
' if you need Excel to quit
wr.Close
Set wr = Nothing
xl.Quit
Set xl = Nothing

Now, this is using late binding, which means no reference to Excel,
which makes it necessary to replace all Excel constants (xlBlahBlah)
with their literal values - or declare your own.

This should also work on any version.

 

[Jeff]

Thanks Roy-

This worked great! Two questions about this:

1. How can I automatically update this file without having the user answer
the question to update the file? (This file has changed, do you want to
exchange it with the new version pop up...)

2. I noticed that I can not open the excel sheet until after I totally close
down the access database. Is there a way to release the excel sheet once the
save to it with wr.save is complete?

Thanks again

 

[RoyVidar]

Thanks Roy-

This worked great! Two questions about this:

1. How can I automatically update this file without having the user
answer the question to update the file? (This file has changed, do
you want to exchange it with the new version pop up...)

2. I noticed that I can not open the excel sheet until after I
totally close down the access database. Is there a way to release
the excel sheet once the save to it with wr.save is complete?

Thanks again

Re 1. wr.Save is supposed to do that, I thought. At least, that's what
happens on my setup.

Re 2. the last four lines are supposed to do that - again, which
is what it does on my setup

 

[Jeff]

Thanks again Roy-

I appreciate the info

----------------

 

[RoyVidar]

Thanks again Roy-

I appreciate the info

Does it work as it should?

If not, are you using my code, have you changed anything, is there
anything we don't know?

Perhaps, if it isn't working, post the code you're using, what
happens, what's supposed to happen etc...

 

[.]

actually iy works great and I plan on using it

thanks again