469,336 Members | 5,570 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,336 developers. It's quick & easy.

acFormatXLS Not Working As Expected

I have the following code associated with a button in an Access form:

stDocName = "Table1"
DoCmd.OutputTo acReport, stDocName, acFormatXLS, "TestFileName.xls"

When I open "TestFileName.xls" and click "File | Save As...", the "Save
as type:" is listed as "Microsoft Excel 5.0/95 Workbook (*.xls)", which
is not what I want. What do I need to do in the Access/VB code to have
"TestFileName.xls" saved as "Microsoft Excel 97 - Excel 2007 & 5.0/95
Workbook (*.xls)"?

Is there anyway that I can specify the following in the Excel
spreadsheet with VB code that is called when the Access button is
clicked?
-Cell [back|fore]ground color
-Cell size
-Cell attributes
-Font

Thanks,
Tim

Jan 22 '07 #1
10 8499
On 22 Jan 2007 03:35:24 -0800, "Tim C" <au******@mail.comwrote:

With VBA code you can do ANYTHING in an Excel document. It's called
Automation. You'll find lots of help online.
Sometimes it makes more sense to setup a template Excel file (*.xlt),
and use it as a starting point rather than a blank one.

-Tom.

>I have the following code associated with a button in an Access form:

stDocName = "Table1"
DoCmd.OutputTo acReport, stDocName, acFormatXLS, "TestFileName.xls"

When I open "TestFileName.xls" and click "File | Save As...", the "Save
as type:" is listed as "Microsoft Excel 5.0/95 Workbook (*.xls)", which
is not what I want. What do I need to do in the Access/VB code to have
"TestFileName.xls" saved as "Microsoft Excel 97 - Excel 2007 & 5.0/95
Workbook (*.xls)"?

Is there anyway that I can specify the following in the Excel
spreadsheet with VB code that is called when the Access button is
clicked?
-Cell [back|fore]ground color
-Cell size
-Cell attributes
-Font

Thanks,
Tim
Jan 22 '07 #2
Thanks for the tip. I did a help search on Automation, but I can't
figure out how to code something like setting font type or
justification. Can you help with a code snippet? Thanks!

Tim

Tom van Stiphout wrote:
On 22 Jan 2007 03:35:24 -0800, "Tim C" <au******@mail.comwrote:

With VBA code you can do ANYTHING in an Excel document. It's called
Automation. You'll find lots of help online.
Sometimes it makes more sense to setup a template Excel file (*.xlt),
and use it as a starting point rather than a blank one.

-Tom.

I have the following code associated with a button in an Access form:

stDocName = "Table1"
DoCmd.OutputTo acReport, stDocName, acFormatXLS, "TestFileName.xls"

When I open "TestFileName.xls" and click "File | Save As...", the "Save
as type:" is listed as "Microsoft Excel 5.0/95 Workbook (*.xls)", which
is not what I want. What do I need to do in the Access/VB code to have
"TestFileName.xls" saved as "Microsoft Excel 97 - Excel 2007 & 5.0/95
Workbook (*.xls)"?

Is there anyway that I can specify the following in the Excel
spreadsheet with VB code that is called when the Access button is
clicked?
-Cell [back|fore]ground color
-Cell size
-Cell attributes
-Font

Thanks,
Tim
Jan 22 '07 #3
On 22 Jan 2007 06:42:49 -0800, "Tim C" <au******@mail.comwrote:

I'll let you in on a secret: Run Excel, record a macro, and go through
the steps to do whatever, e.g. setting font type or justification.
Stop the recorder. Inspect the VBA code just created. Use it in your
Automation code.

-Tom.

>Thanks for the tip. I did a help search on Automation, but I can't
figure out how to code something like setting font type or
justification. Can you help with a code snippet? Thanks!

Tim

Tom van Stiphout wrote:
>On 22 Jan 2007 03:35:24 -0800, "Tim C" <au******@mail.comwrote:

With VBA code you can do ANYTHING in an Excel document. It's called
Automation. You'll find lots of help online.
Sometimes it makes more sense to setup a template Excel file (*.xlt),
and use it as a starting point rather than a blank one.

-Tom.

>I have the following code associated with a button in an Access form:

stDocName = "Table1"
DoCmd.OutputTo acReport, stDocName, acFormatXLS, "TestFileName.xls"

When I open "TestFileName.xls" and click "File | Save As...", the "Save
as type:" is listed as "Microsoft Excel 5.0/95 Workbook (*.xls)", which
is not what I want. What do I need to do in the Access/VB code to have
"TestFileName.xls" saved as "Microsoft Excel 97 - Excel 2007 & 5.0/95
Workbook (*.xls)"?

Is there anyway that I can specify the following in the Excel
spreadsheet with VB code that is called when the Access button is
clicked?
-Cell [back|fore]ground color
-Cell size
-Cell attributes
-Font

Thanks,
Tim
Jan 23 '07 #4
That was pretty cool..., but I ran into a snag. I need the code to run
under VB via Access. It appears that Excel VB and Access VB are
different. When I copy and paste the Excel VB code into Access VB,
save, then hit the Access form button that calls the code I get error
message, "Compile error: Sub or Function not defined". The highlighted
code is on "Range" of the following line:

Range ("C1").Select

I've been searching through Help, but I can't figure out how to get
Access VB to manipulate an Excel ss.

Anymore suggestions?

Thanks!

Tom van Stiphout wrote:
On 22 Jan 2007 06:42:49 -0800, "Tim C" <au******@mail.comwrote:

I'll let you in on a secret: Run Excel, record a macro, and go through
the steps to do whatever, e.g. setting font type or justification.
Stop the recorder. Inspect the VBA code just created. Use it in your
Automation code.

-Tom.

Thanks for the tip. I did a help search on Automation, but I can't
figure out how to code something like setting font type or
justification. Can you help with a code snippet? Thanks!

Tim

Tom van Stiphout wrote:
On 22 Jan 2007 03:35:24 -0800, "Tim C" <au******@mail.comwrote:

With VBA code you can do ANYTHING in an Excel document. It's called
Automation. You'll find lots of help online.
Sometimes it makes more sense to setup a template Excel file (*.xlt),
and use it as a starting point rather than a blank one.

-Tom.
I have the following code associated with a button in an Access form:

stDocName = "Table1"
DoCmd.OutputTo acReport, stDocName, acFormatXLS, "TestFileName.xls"

When I open "TestFileName.xls" and click "File | Save As...", the "Save
as type:" is listed as "Microsoft Excel 5.0/95 Workbook (*.xls)", which
is not what I want. What do I need to do in the Access/VB code to have
"TestFileName.xls" saved as "Microsoft Excel 97 - Excel 2007 & 5.0/95
Workbook (*.xls)"?

Is there anyway that I can specify the following in the Excel
spreadsheet with VB code that is called when the Access button is
clicked?
-Cell [back|fore]ground color
-Cell size
-Cell attributes
-Font

Thanks,
Tim
Jan 23 '07 #5

Tim C wrote:
Range ("C1").Select

Anymore suggestions?
Tom gave you great advice. Here's another secret:

When referring to Excel ranges from within Access you need the
equivalent of a full path name when using the object model.

E.g.,

Set objXL = CreateObject("Excel.Application")
objXL.Visible = True
Set wsheet = objXL.Worksheets("Active Employees by Dept")
With wsheet
For lngI = 1 To lngRecordCount
'merge the fields
strRange = "A" & CStr(1 + lngI) & ":C" & CStr(1 + lngI)
.Range(strRange).MergeCells = True
.Range(strRange).Select
.Application.Selection.Font.Bold = True
With .Application.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = True
End With
....

Set wsheet = Nothing
objXL.Quit
Set objXL = Nothing

Cleaning up the references so that Excel doesn't get stuck open is
another secret.

James A. Fortune
CD********@FortuneJames.com

Jan 23 '07 #6
On Jan 23, 4:16 pm, CDMAPos...@FortuneJames.com wrote:
Tim C wrote:
Range ("C1").Select
Anymore suggestions?Tom gave you great advice. Here's another secret:

When referring to Excel ranges from within Access you need the
equivalent of a full path name when using the object model.

E.g.,
Cool stuff. Thanks!
Set objXL = CreateObject("Excel.Application")
objXL.Visible = True
Set wsheet = objXL.Worksheets("Active Employees by Dept")
With wsheet
For lngI = 1 To lngRecordCount
'merge the fields
strRange = "A" & CStr(1 + lngI) & ":C" & CStr(1 + lngI)
.Range(strRange).MergeCells = True
I tried the "MergeCells" with an existing spreadsheet and I had to
have the ss open to see what was going on (had some different/extra
code to do that...). The "MergeCells" method (that is a method, yes?)
prompted me to acknowledge the change in the ss. What code would force
the ss do the merge without the prompt?

Is there a book or website that has a list of the methods I can use
for ss automation? When I try to lookup "MergeCells" in the VB help I
get "Keyword Not Found"...
.Range(strRange).Select
.Application.Selection.Font.Bold = True
With .Application.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = True
End With
...

Set wsheet = Nothing
objXL.Quit
When I tried to run the above line I got, "Object doesn't support this
property or method". My VB help doesn't have much help/information on
the methods I need help with. Where can I find out what ".Quit" does?
Is there a web reference somewhere?
Set objXL = Nothing
Let's say that I open a ss with code from Access VB and modify the ss.
How do I have VB save the modified ss?
Cleaning up the references so that Excel doesn't get stuck open is
another secret.
Where do I find that secret :) ?

Thanks!
Tim
James A. Fortune
CDMAPos...@FortuneJames.com
Jan 30 '07 #7
On Jan 30, 9:26 am, "Tim C" <ausfa...@mail.comwrote:
On Jan 23, 4:16 pm, CDMAPos...@FortuneJames.com wrote:
Tim C wrote:
Range ("C1").Select
Anymore suggestions?Tom gave you great advice. Here's another secret:
When referring to Excel ranges from within Access you need the
equivalent of a full path name when using the object model.
E.g.,Cool stuff. Thanks!
Set objXL = CreateObject("Excel.Application")
objXL.Visible = True
Set wsheet = objXL.Worksheets("Active Employees by Dept")
With wsheet
For lngI = 1 To lngRecordCount
'merge the fields
strRange = "A" & CStr(1 + lngI) & ":C" & CStr(1 + lngI)
.Range(strRange).MergeCells = TrueI tried the "MergeCells" with an existing spreadsheet and I had to
have the ss open to see what was going on (had some different/extra
code to do that...). The "MergeCells" method (that is a method, yes?)
prompted me to acknowledge the change in the ss. What code would force
the ss do the merge without the prompt?

Is there a book or website that has a list of the methods I can use
for ss automation? When I try to lookup "MergeCells" in the VB help I
get "Keyword Not Found"...
MergeCells is a method in at least Excel 97. I didn't even know it
existed until I recorded a macro in Excel to do what I wanted. When
you get done recording your macro, rest assured that those commands
will exist in your version of Excel.

See:

How to find and use Office object model documentation

http://support.microsoft.com/default...scid=kb;en-us;
222101&Product=acc

Super-Easy Guide to the Microsoft Office Excel 2003 Object Model

http://msdn2.microsoft.com/en-us/lib...ffice.11).aspx
>
.Range(strRange).Select
.Application.Selection.Font.Bold = True
With .Application.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = True
End With
...
Set wsheet = Nothing
objXL.QuitWhen I tried to run the above line I got, "Object doesn't support this
property or method". My VB help doesn't have much help/information on
the methods I need help with. Where can I find out what ".Quit" does?
Is there a web reference somewhere?
Don't use .Quit unless it shows up in your recorded macro.
>
Set objXL = NothingLet's say that I open a ss with code from Access VB and modify the ss.
How do I have VB save the modified ss?
Make the save operation part of the recorded macro. Replace the save
name with a string variable.
>
Cleaning up the references so that Excel doesn't get stuck open is
another secret.Where do I find that secret :) ?
See:

Access not releasing Excel

http://groups.google.com/group/comp....ms-access/msg/
57b06a7c19716e58

James A. Fortune
CD********@FortuneJames.com

Jan 30 '07 #8
On Jan 30, 6:48 pm, CDMAPos...@FortuneJames.com wrote:
On Jan 30, 9:26 am, "Tim C" <ausfa...@mail.comwrote:


On Jan 23, 4:16 pm, CDMAPos...@FortuneJames.com wrote:
Tim C wrote:
Range ("C1").Select
Anymore suggestions?Tom gave you great advice. Here's another secret:
When referring to Excel ranges from within Access you need the
equivalent of a full path name when using the object model.
E.g.,Cool stuff. Thanks!
Set objXL = CreateObject("Excel.Application")
objXL.Visible = True
Set wsheet = objXL.Worksheets("Active Employees by Dept")
With wsheet
For lngI = 1 To lngRecordCount
'merge the fields
strRange = "A" & CStr(1 + lngI) & ":C" & CStr(1 + lngI)
.Range(strRange).MergeCells = TrueI tried the "MergeCells" with an existing spreadsheet and I had to
have the ss open to see what was going on (had some different/extra
code to do that...). The "MergeCells" method (that is a method, yes?)
prompted me to acknowledge the change in the ss. What code would force
the ss do the merge without the prompt?
Is there a book or website that has a list of the methods I can use
for ss automation? When I try to lookup "MergeCells" in the VB help I
get "Keyword Not Found"...

MergeCells is a method in at least Excel 97. I didn't even know it
existed until I recorded a macro in Excel to do what I wanted. When
you get done recording your macro, rest assured that those commands
will exist in your version of Excel.

See:

How to find and use Office object model documentation

http://support.microsoft.com/default...scid=kb;en-us;
222101&Product=acc

Super-Easy Guide to the Microsoft Office Excel 2003 Object Model

http://msdn2.microsoft.com/en-us/lib...ffice.11).aspx


.Range(strRange).Select
.Application.Selection.Font.Bold = True
With .Application.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = True
End With
...
Set wsheet = Nothing
objXL.QuitWhen I tried to run the above line I got, "Object doesn't support this
property or method". My VB help doesn't have much help/information on
the methods I need help with. Where can I find out what ".Quit" does?
Is there a web reference somewhere?

Don't use .Quit unless it shows up in your recorded macro.
Set objXL = NothingLet's say that I open a ss with code from Access VB and modify the ss.
How do I have VB save the modified ss?

Make the save operation part of the recorded macro. Replace the save
name with a string variable.
I'm thinking about shaving my head because I'm pulling my hair out
trying to figure out how to code a "save spreadsheet" from within
Access VB... The Excel macros are really cool, but trying to translate
the Excel VB macro code to Access VB has got me thinking someone,
possibly me, gave me a frontal lobotomy...

Here's what I did:
1. Fired up a blank Excel ss.
2. Started recording a macro.
3. Saved the ss.
4. Stopped recording the macro and got the following code:
Sub Save()
ActiveWorkbook.Save
End Sub

I think I've tried every variation of integrating
"ActiveWorkbook.Save" into my Access VB code except for the correct
syntax. I know you suggested replacing the "save name", but what is
the "save name" and what string variable would I use? Thanks!
--
Tim
Cleaning up the references so that Excel doesn't get stuck open is
another secret.Where do I find that secret :) ?

See:

Access not releasing Excel

http://groups.google.com/group/comp....ms-access/msg/
57b06a7c19716e58

James A. Fortune
CDMAPos...@FortuneJames.com- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Feb 2 '07 #9
On Feb 2, 8:57 am, "Tim C" <ausfa...@mail.comwrote:
I'm thinking about shaving my head because I'm pulling my hair out
trying to figure out how to code a "save spreadsheet" from within
Access VB... The Excel macros are really cool, but trying to translate
the Excel VB macro code to Access VB has got me thinking someone,
possibly me, gave me a frontal lobotomy...

Here's what I did:
1. Fired up a blank Excel ss.
2. Started recording a macro.
3. Saved the ss.
4. Stopped recording the macro and got the following code:
Sub Save()
ActiveWorkbook.Save
End Sub

I think I've tried every variation of integrating
"ActiveWorkbook.Save" into my Access VB code except for the correct
syntax. I know you suggested replacing the "save name", but what is
the "save name" and what string variable would I use? Thanks!
--
Tim

The example I was thinking of when I said that, opens an Excel
template (just an ordinary Excel spreadsheet that's pre-formatted) and
does a "SaveAs" to get an xls file with the name and location I want.
I'm guessing that ActiveWorkbook.Save belongs to the Excel.Application
object. For example,

objXL.ActiveWorkbook.Save

Since you opened a blank (new) Excel spreadsheet, I think the save
name you chose should have shown up in the macro.

I just tried a Save with Excel 97. Here's what got recorded:

ChDir "C:\"
ActiveWorkbook.SaveAs FileName:="C:\Book1.xls", FileFormat:=xlNormal,
_
Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, _
CreateBackup:=False

Most of these options are simply default options.

Some Access code might look like:

'Create the file
Set objXL = CreateObject("Excel.Application")
Set wbk = objXL.Workbooks.Open(strTemplate)
wbk.SaveAs strFQP
DoEvents
'Give the OS 1.5 seconds to save the copy
Call MilliSleep(1500)
objXL.Visible = True
....
Set MyDB = Nothing
Set wsheet = Nothing
wbk.Save
DoEvents
Set wbk = Nothing
lblStatus.Caption = ""
DoEvents
'Give the OS 2.0 seconds to save the changes
Call MilliSleep(2000)
objXL.Quit
Set objXL = Nothing
MsgBox ("Done creating " & strFQP)

The early binding Dim's were:

Dim objXL As Excel.Application
Dim wbk As Excel.Workbook
Dim wsheet As Excel.Worksheet

I think I got MilliSleep() from this NG. I don't remember from whom.
It doesn't look that difficult. There were no comments stating that
they must not be removed. Module code:

Public Declare Sub MilliSleep Lib "kernel32" Alias "Sleep" (ByVal
dwMilliseconds As Long)

I hope this helps,

James A. Fortune
CD********@FortuneJames.com

Feb 2 '07 #10
On Feb 2, 5:23 pm, CDMAPos...@FortuneJames.com wrote:
On Feb 2, 8:57 am, "Tim C" <ausfa...@mail.comwrote:


I'm thinking about shaving my head because I'm pulling my hair out
trying to figure out how to code a "save spreadsheet" from within
Access VB... The Excel macros are really cool, but trying to translate
the Excel VB macro code to Access VB has got me thinking someone,
possibly me, gave me a frontal lobotomy...
Here's what I did:
1. Fired up a blank Excel ss.
2. Started recording a macro.
3. Saved the ss.
4. Stopped recording the macro and got the following code:
Sub Save()
ActiveWorkbook.Save
End Sub
I think I've tried every variation of integrating
"ActiveWorkbook.Save" into my Access VB code except for the correct
syntax. I know you suggested replacing the "save name", but what is
the "save name" and what string variable would I use? Thanks!
--
Tim

The example I was thinking of when I said that, opens an Excel
template (just an ordinary Excel spreadsheet that's pre-formatted) and
does a "SaveAs" to get an xls file with the name and location I want.
I'm guessing that ActiveWorkbook.Save belongs to the Excel.Application
object. For example,

objXL.ActiveWorkbook.Save

Since you opened a blank (new) Excel spreadsheet, I think the save
name you chose should have shown up in the macro.

I just tried a Save with Excel 97. Here's what got recorded:

ChDir "C:\"
ActiveWorkbook.SaveAs FileName:="C:\Book1.xls", FileFormat:=xlNormal,
_
Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, _
CreateBackup:=False

Most of these options are simply default options.

Some Access code might look like:

'Create the file
Set objXL = CreateObject("Excel.Application")
Set wbk = objXL.Workbooks.Open(strTemplate)
wbk.SaveAs strFQP
DoEvents
'Give the OS 1.5 seconds to save the copy
Call MilliSleep(1500)
objXL.Visible = True
...
Set MyDB = Nothing
Set wsheet = Nothing
wbk.Save
DoEvents
Set wbk = Nothing
lblStatus.Caption = ""
DoEvents
'Give the OS 2.0 seconds to save the changes
Call MilliSleep(2000)
objXL.Quit
Set objXL = Nothing
MsgBox ("Done creating " & strFQP)

The early binding Dim's were:

Dim objXL As Excel.Application
Dim wbk As Excel.Workbook
Dim wsheet As Excel.Worksheet

I think I got MilliSleep() from this NG. I don't remember from whom.
It doesn't look that difficult. There were no comments stating that
they must not be removed. Module code:

Public Declare Sub MilliSleep Lib "kernel32" Alias "Sleep" (ByVal
dwMilliseconds As Long)

I hope this helps,

James A. Fortune
CDMAPos...@FortuneJames.com- Hide quoted text -

- Show quoted text -
That helped out! I think I'm getting it now. Thanks!

Feb 8 '07 #11

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

10 posts views Thread by Scott Richards | last post: by
2 posts views Thread by Trevor | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Marylou17 | last post: by
1 post views Thread by Marylou17 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.