Prev: Filedialog err 438 Object doesn't support this property or method
Next: DoCmd.RunCommand acCmdFind
From: Klatuu on 10 Nov 2006 17:29
No, it works as is. I have seen other posts stating the xl constants are
only available in late binding, but I have found that not to be true. In
fact, I can go into the immediate window without an instance of Excel running
and query an xl constant and it returns the correct value. Maybe it could be
because I have the Excel 11.0 object library in my references.

I use late binding because when this particular code was written, we had
some users on Office 2000 and some on 2003. Early binding in that case
causes one or the other not to work because the object libraries are
different.

As to the cells, I don't know what you mean. Cells is a property of both
the Worksheet and Range objects.

"RoyVidar" wrote:

> "Klatuu" <Klatuu(a)discussions.microsoft.com> wrote in message
> <1042F6C8-8ECD-4446-8143-79D128205904(a)microsoft.com>:
>
> Very nice code, Klatuu, I'm sure you have a declaration section
> where you declare all the xlConstants, don't you? (probably also
> contains declaration of "cell", too?)
>
> Else there'd probably be some challenges going late bound.
>
> --
> Roy-Vidar
>
>
>
From: BruceS bruce at on 10 Nov 2006 17:30
Thanks, but I can't take credit. Had help from two different responders, one
from Access Programming and one from Excel General Questions, on how to set
up editing and saving.

Also, thanks for the tip on the Range vs. Selection. Didn't know that.

Bruce

"Klatuu" wrote:

> Nice code, Bruce.
> I would make one suggestion. The Selection object in Automation can get
> squirly on you. It is really better to use the Range object. It seems to be
> more stable.
>
> "BruceS" wrote:
>
> > Andreas,
> >
> > Had a similar need just last week. You can open the spreadsheet and make
> > the format changes all from VBA. Here is my code, which works in A2K. Maybe
> > you can take something from it.
> >
> > Bruce
> >
> > Sub SetSpreadsheetHeadings( _
> > forFilePath As String, _
> > Optional tabName As String)
> >
> > On Error GoTo Proc_Err
> > '
> > ' Sets headings for new spreadsheet.
> > '
> > Dim xlApp As Excel.Application
> > Dim wb As Excel.Workbook
> > Dim bolLeaveOpen As Boolean
> >
> > If IsMissing(tabName) Then tabName = ""
> >
> > 'If Excel is already open, use that instance
> > bolLeaveOpen = True
> >
> > 'Attempting to use something that is not available
> > ' will generate an error.
> > On Error Resume Next
> > Set xlApp = GetObject(, "Excel.Application")
> > Err.Clear
> >
> > On Error GoTo Proc_Err
> >
> > 'If xlApp is defined, then we already have a conversation open
> > If TypeName(xlApp) = "Nothing" Then
> > bolLeaveOpen = False
> > 'Excel was not open -- create a new instance
> > Set xlApp = CreateObject("Excel.Application")
> > End If
> >
> > 'Keep any open workbooks from running any macros while I'm using it.
> > xlApp.EnableEvents = False
> >
> > 'Open workbook just created.
> > Set wb = xlApp.Workbooks.Open(forFilePath)
> >
> > 'Keep the workbook from running macros while I use it.
> > xlApp.EnableEvents = False
> >
> > 'Rename tab.
> > wb.Worksheets("ExportTemp").Select
> > If tabName > "" Then
> > wb.Worksheets("ExportTemp").Name = tabName
> > Else
> > tabName = "ExportTemp"
> > End If
> >
> > 'Select headings row and format.
> > wb.Worksheets(tabName).Rows("1:1").Select
> > With xlApp.Selection
> > .Font.FontStyle = "Bold"
> > .Borders(xlDiagonalDown).LineStyle = xlNone
> > .Borders(xlDiagonalUp).LineStyle = xlNone
> > .Borders(xlEdgeLeft).LineStyle = xlNone
> > .Borders(xlEdgeTop).LineStyle = xlNone
> > With .Borders(xlEdgeBottom)
> > .LineStyle = xlContinuous
> > .Weight = xlThin
> > .ColorIndex = xlAutomatic
> > End With
> > .Borders(xlEdgeRight).LineStyle = xlNone
> > .Borders(xlInsideVertical).LineStyle = xlNone
> > With .Interior
> > .ColorIndex = 15
> > .Pattern = xlSolid
> > .PatternColorIndex = xlAutomatic
> > End With
> > End With
> >
> > 'Set all columns to best width.
> > wb.Worksheets(tabName).Cells.Select
> > xlApp.Selection.Columns.AutoFit
> >
> > 'Deselect heading row by selecting single cell.
> > wb.Worksheets(tabName).Range("A2").Select
> >
> > 'Save changes, then be sure they are saved before continuing.
> > wb.Save
> > DoEvents
> >
> > 'Close this specific workbook.
> > wb.Close False
> >
> > 'Turn macros back on for any workbooks still open.
> > xlApp.EnableEvents = True
> >
> > Proc_Exit:
> > On Error Resume Next
> >
> > If TypeName(xlApp) <> "Nothing" Then
> > If Not bolLeaveOpen Then xlApp.Quit
> > End If
> >
> > Set wb = Nothing
> > Set xlApp = Nothing
> >
> > Err.Clear
> > Exit Sub
> >
> > Proc_Err:
> > MsgBox "Error editing spreadsheet:" & vbCr & vbCr & _
> > "Error Code: " & Err.Number & vbCr & _
> > Err.Description, vbOKCritical, "Error!"
> > Err.Clear
> >
> > Resume Proc_Exit
> >
> > End Sub
> >
> >
> > "andreas.strzodka(a)ny.frb.org" wrote:
> >
> > > Hello,
> > >
> > > I am exporting a spreadsheet from an Access database and I want to
> > > format the excel file. Thus, I have written to pieces of VBA code, one
> > > in Access, one in Excel. Does anybody have any ideas on how to combine
> > > them. I have looked up several solutions, yet have not found a workable
> > > one.
> > >
> > > The Access Code:
> > > Sub Request_Export_Click()
> > > Dim datestr As String
> > >
> > > datestr = Me.File_Date
> > >
> > > DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "ACCOUNTS
> > > In", "H:\HS Details " & datestr & ".xls"
> > > DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "ACCOUNTS
> > > Out", "H:\HS Details " & datestr & ".xls"
> > >
> > > End Sub
> > >
> > > The Excel Code:
> > > Sub format_worksheet()
> > >
> > > Columns("a:a").ColumnWidth = 7.5
> > > Columns("b:ao").ColumnWidth = 15
> > >
> > > Cells.Select
> > > With Selection
> > > .Font.Name = "Arial"
> > > .Font.Size = 8
> > > End With
> > >
> > > Range("a1:ao600").Select
> > > With Selection
> > > .WrapText = True
> > > .ShrinkToFit = True
> > > End With
> > >
> > > Range("a1:ao1").Select
> > > With Selection
> > > .HorizontalAlignment = xlCenter
> > > End With
> > >
> > > End Sub
> > >
> > > Thanks,
> > >
> > > Andreas
> > >
> > >
From: Klatuu on 10 Nov 2006 17:44
I went back and looked more closely at the cell issue. My brain, at first
read cells. Now looking at it, I wonder why it works. It has been in
production since March, 2005. That's weird. I don't even find any refernce
to a cell object or property in help or the object browser.

Well, maybe I discovered something. I promise it works.

"RoyVidar" wrote:

> "Klatuu" <Klatuu(a)discussions.microsoft.com> wrote in message
> <1042F6C8-8ECD-4446-8143-79D128205904(a)microsoft.com>:
>
> Very nice code, Klatuu, I'm sure you have a declaration section
> where you declare all the xlConstants, don't you? (probably also
> contains declaration of "cell", too?)
>
> Else there'd probably be some challenges going late bound.
>
> --
> Roy-Vidar
>
>
>
From: BruceS bruce at on 10 Nov 2006 17:47
Klatuu,
Wanted everyone to know that it was "strive4peace" ("Crystal") and Roy Vidar
that really helped with my Excel problem last week. Kudos to both!
Bruce

"Klatuu" wrote:

> Nice code, Bruce.
> I would make one suggestion. The Selection object in Automation can get
> squirly on you. It is really better to use the Range object. It seems to be
> more stable.
>
> "BruceS" wrote:
>
> > Andreas,
> >
> > Had a similar need just last week. You can open the spreadsheet and make
> > the format changes all from VBA. Here is my code, which works in A2K. Maybe
> > you can take something from it.
> >
> > Bruce
> >
> > Sub SetSpreadsheetHeadings( _
> > forFilePath As String, _
> > Optional tabName As String)
> >
> > On Error GoTo Proc_Err
> > '
> > ' Sets headings for new spreadsheet.
> > '
> > Dim xlApp As Excel.Application
> > Dim wb As Excel.Workbook
> > Dim bolLeaveOpen As Boolean
> >
> > If IsMissing(tabName) Then tabName = ""
> >
> > 'If Excel is already open, use that instance
> > bolLeaveOpen = True
> >
> > 'Attempting to use something that is not available
> > ' will generate an error.
> > On Error Resume Next
> > Set xlApp = GetObject(, "Excel.Application")
> > Err.Clear
> >
> > On Error GoTo Proc_Err
> >
> > 'If xlApp is defined, then we already have a conversation open
> > If TypeName(xlApp) = "Nothing" Then
> > bolLeaveOpen = False
> > 'Excel was not open -- create a new instance
> > Set xlApp = CreateObject("Excel.Application")
> > End If
> >
> > 'Keep any open workbooks from running any macros while I'm using it.
> > xlApp.EnableEvents = False
> >
> > 'Open workbook just created.
> > Set wb = xlApp.Workbooks.Open(forFilePath)
> >
> > 'Keep the workbook from running macros while I use it.
> > xlApp.EnableEvents = False
> >
> > 'Rename tab.
> > wb.Worksheets("ExportTemp").Select
> > If tabName > "" Then
> > wb.Worksheets("ExportTemp").Name = tabName
> > Else
> > tabName = "ExportTemp"
> > End If
> >
> > 'Select headings row and format.
> > wb.Worksheets(tabName).Rows("1:1").Select
> > With xlApp.Selection
> > .Font.FontStyle = "Bold"
> > .Borders(xlDiagonalDown).LineStyle = xlNone
> > .Borders(xlDiagonalUp).LineStyle = xlNone
> > .Borders(xlEdgeLeft).LineStyle = xlNone
> > .Borders(xlEdgeTop).LineStyle = xlNone
> > With .Borders(xlEdgeBottom)
> > .LineStyle = xlContinuous
> > .Weight = xlThin
> > .ColorIndex = xlAutomatic
> > End With
> > .Borders(xlEdgeRight).LineStyle = xlNone
> > .Borders(xlInsideVertical).LineStyle = xlNone
> > With .Interior
> > .ColorIndex = 15
> > .Pattern = xlSolid
> > .PatternColorIndex = xlAutomatic
> > End With
> > End With
> >
> > 'Set all columns to best width.
> > wb.Worksheets(tabName).Cells.Select
> > xlApp.Selection.Columns.AutoFit
> >
> > 'Deselect heading row by selecting single cell.
> > wb.Worksheets(tabName).Range("A2").Select
> >
> > 'Save changes, then be sure they are saved before continuing.
> > wb.Save
> > DoEvents
> >
> > 'Close this specific workbook.
> > wb.Close False
> >
> > 'Turn macros back on for any workbooks still open.
> > xlApp.EnableEvents = True
> >
> > Proc_Exit:
> > On Error Resume Next
> >
> > If TypeName(xlApp) <> "Nothing" Then
> > If Not bolLeaveOpen Then xlApp.Quit
> > End If
> >
> > Set wb = Nothing
> > Set xlApp = Nothing
> >
> > Err.Clear
> > Exit Sub
> >
> > Proc_Err:
> > MsgBox "Error editing spreadsheet:" & vbCr & vbCr & _
> > "Error Code: " & Err.Number & vbCr & _
> > Err.Description, vbOKCritical, "Error!"
> > Err.Clear
> >
> > Resume Proc_Exit
> >
> > End Sub
> >
> >
> > "andreas.strzodka(a)ny.frb.org" wrote:
> >
> > > Hello,
> > >
> > > I am exporting a spreadsheet from an Access database and I want to
> > > format the excel file. Thus, I have written to pieces of VBA code, one
> > > in Access, one in Excel. Does anybody have any ideas on how to combine
> > > them. I have looked up several solutions, yet have not found a workable
> > > one.
> > >
> > > The Access Code:
> > > Sub Request_Export_Click()
> > > Dim datestr As String
> > >
> > > datestr = Me.File_Date
> > >
> > > DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "ACCOUNTS
> > > In", "H:\HS Details " & datestr & ".xls"
> > > DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "ACCOUNTS
> > > Out", "H:\HS Details " & datestr & ".xls"
> > >
> > > End Sub
> > >
> > > The Excel Code:
> > > Sub format_worksheet()
> > >
> > > Columns("a:a").ColumnWidth = 7.5
> > > Columns("b:ao").ColumnWidth = 15
> > >
> > > Cells.Select
> > > With Selection
> > > .Font.Name = "Arial"
> > > .Font.Size = 8
> > > End With
> > >
> > > Range("a1:ao600").Select
> > > With Selection
> > > .WrapText = True
> > > .ShrinkToFit = True
> > > End With
> > >
> > > Range("a1:ao1").Select
> > > With Selection
> > > .HorizontalAlignment = xlCenter
> > > End With
> > >
> > > End Sub
> > >
> > > Thanks,
> > >
> > > Andreas
> > >
> > >
From: Klatuu on 10 Nov 2006 17:53
They are both good. Excel automation is a very different beast and takes
some special handling to avoid problems, but it is a very powerful tool. I
have had to spend a lot of time with it where I am because the love their
reports done in Excel. Nobody ever uses the printed version.

If you are following this post, Roy found something in my code he
questioned. The part about the Excel constants is understandable, but the
part about the cell I don't know. Since it has been almost 2 years since I
wrote it, I don't remember how I got the idea to use it or why it works.

"BruceS" wrote:

> Klatuu,
> Wanted everyone to know that it was "strive4peace" ("Crystal") and Roy Vidar
> that really helped with my Excel problem last week. Kudos to both!
> Bruce
>
> "Klatuu" wrote:
>
> > Nice code, Bruce.
> > I would make one suggestion. The Selection object in Automation can get
> > squirly on you. It is really better to use the Range object. It seems to be
> > more stable.
> >
> > "BruceS" wrote:
> >
> > > Andreas,
> > >
> > > Had a similar need just last week. You can open the spreadsheet and make
> > > the format changes all from VBA. Here is my code, which works in A2K. Maybe
> > > you can take something from it.
> > >
> > > Bruce
> > >
> > > Sub SetSpreadsheetHeadings( _
> > > forFilePath As String, _
> > > Optional tabName As String)
> > >
> > > On Error GoTo Proc_Err
> > > '
> > > ' Sets headings for new spreadsheet.
> > > '
> > > Dim xlApp As Excel.Application
> > > Dim wb As Excel.Workbook
> > > Dim bolLeaveOpen As Boolean
> > >
> > > If IsMissing(tabName) Then tabName = ""
> > >
> > > 'If Excel is already open, use that instance
> > > bolLeaveOpen = True
> > >
> > > 'Attempting to use something that is not available
> > > ' will generate an error.
> > > On Error Resume Next
> > > Set xlApp = GetObject(, "Excel.Application")
> > > Err.Clear
> > >
> > > On Error GoTo Proc_Err
> > >
> > > 'If xlApp is defined, then we already have a conversation open
> > > If TypeName(xlApp) = "Nothing" Then
> > > bolLeaveOpen = False
> > > 'Excel was not open -- create a new instance
> > > Set xlApp = CreateObject("Excel.Application")
> > > End If
> > >
> > > 'Keep any open workbooks from running any macros while I'm using it.
> > > xlApp.EnableEvents = False
> > >
> > > 'Open workbook just created.
> > > Set wb = xlApp.Workbooks.Open(forFilePath)
> > >
> > > 'Keep the workbook from running macros while I use it.
> > > xlApp.EnableEvents = False
> > >
> > > 'Rename tab.
> > > wb.Worksheets("ExportTemp").Select
> > > If tabName > "" Then
> > > wb.Worksheets("ExportTemp").Name = tabName
> > > Else
> > > tabName = "ExportTemp"
> > > End If
> > >
> > > 'Select headings row and format.
> > > wb.Worksheets(tabName).Rows("1:1").Select
> > > With xlApp.Selection
> > > .Font.FontStyle = "Bold"
> > > .Borders(xlDiagonalDown).LineStyle = xlNone
> > > .Borders(xlDiagonalUp).LineStyle = xlNone
> > > .Borders(xlEdgeLeft).LineStyle = xlNone
> > > .Borders(xlEdgeTop).LineStyle = xlNone
> > > With .Borders(xlEdgeBottom)
> > > .LineStyle = xlContinuous
> > > .Weight = xlThin
> > > .ColorIndex = xlAutomatic
> > > End With
> > > .Borders(xlEdgeRight).LineStyle = xlNone
> > > .Borders(xlInsideVertical).LineStyle = xlNone
> > > With .Interior
> > > .ColorIndex = 15
> > > .Pattern = xlSolid
> > > .PatternColorIndex = xlAutomatic
> > > End With
> > > End With
> > >
> > > 'Set all columns to best width.
> > > wb.Worksheets(tabName).Cells.Select
> > > xlApp.Selection.Columns.AutoFit
> > >
> > > 'Deselect heading row by selecting single cell.
> > > wb.Worksheets(tabName).Range("A2").Select
> > >
> > > 'Save changes, then be sure they are saved before continuing.
> > > wb.Save
> > > DoEvents
> > >
> > > 'Close this specific workbook.
> > > wb.Close False
> > >
> > > 'Turn macros back on for any workbooks still open.
> > > xlApp.EnableEvents = True
> > >
> > > Proc_Exit:
> > > On Error Resume Next
> > >
> > > If TypeName(xlApp) <> "Nothing" Then
> > > If Not bolLeaveOpen Then xlApp.Quit
> > > End If
> > >
> > > Set wb = Nothing
> > > Set xlApp = Nothing
> > >
> > > Err.Clear
> > > Exit Sub
> > >
> > > Proc_Err:
> > > MsgBox "Error editing spreadsheet:" & vbCr & vbCr & _
> > > "Error Code: " & Err.Number & vbCr & _
> > > Err.Description, vbOKCritical, "Error!"
> > > Err.Clear
> > >
> > > Resume Proc_Exit
> > >
> > > End Sub
> > >
> > >
> > > "andreas.strzodka(a)ny.frb.org" wrote:
> > >
> > > > Hello,
> > > >
> > > > I am exporting a spreadsheet from an Access database and I want to
> > > > format the excel file. Thus, I have written to pieces of VBA code, one
> > > > in Access, one in Excel. Does anybody have any ideas on how to combine
> > > > them. I have looked up several solutions, yet have not found a workable
> > > > one.
> > > >
> > > > The Access Code:
> > > > Sub Request_Export_Click()
> > > > Dim datestr As String
> > > >
> > > > datestr = Me.File_Date
> > > >
> > > > DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "ACCOUNTS
> > > > In", "H:\HS Details " & datestr & ".xls"
> > > > DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "ACCOUNTS
> > > > Out", "H:\HS Details " & datestr & ".xls"
> > > >
> > > > End Sub
> > > >
> > > > The Excel Code:
> > > > Sub format_worksheet()
> > > >
> > > > Columns("a:a").ColumnWidth = 7.5
> > > > Columns("b:ao").ColumnWidth = 15
> > > >
> > > > Cells.Select
> > > > With Selection
> > > > .Font.Name = "Arial"
> > > > .Font.Size = 8
> > > > End With
> > > >
> > > > Range("a1:ao600").Select
> > > > With Selection
> > > > .WrapText = True
> > > > .ShrinkToFit = True
> > > > End With
> > > >
> > > > Range("a1:ao1").Select
> > > > With Selection
> > > > .HorizontalAlignment = xlCenter
> > > > End With
> > > >
> > > > End Sub
> > > >
> > > > T
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4 5
Prev: Filedialog err 438 Object doesn't support this property or method
Next: DoCmd.RunCommand acCmdFind