Create Excel workbook from code

[GraeagleBill]

The code below runs successfully in both 2003 and 2010 environments. However, in the 2010 (Win 7 Pro) environment, it's necessary to open the xls file with Excel 2010 and then save it in the Excel 97-2003 format before it's accepted as an xls file in other applications, like an upload to a telephone calling service. What changes, if there are any, that can be included/added to my code that will get the xls file in the proper format when created from within the 2010 environment. As it is now, the fronend mde file is created in the 2003 environment.

Thanks,
Bill

Private Sub OutputToExcel()
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object



'Start a new workbook in Excel
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add

Set oSheet = oBook.Worksheets(1)

'Transfer the array to the worksheet starting at cell A1 (Column headings already in 1st row of strBCarray)
oSheet.Range("A1").Resize(iRow, jCol).Value = strBCarray

'Save the Workbook and Quit Excel
Kill (XLSName)
oBook.SaveAs XLSName
oExcel.Quit
End Sub

[June7]

Variables XLSName, iRow, JCol, strBCarry are not declared nor set. Their values are 0 or empty. Is that what you want?

Review: http://blogs.office.com/b/microsoft-...xcel-2007.aspx

Try:
oBook.SaveAs XLSName, 56

[GraeagleBill]

The variables you mention are global to the module:


Option Compare Database
Option Base 1
Option Explicit
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=* =*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=* =*=*=*=*
' The purpose of this module is to establish an Excel file containing the groups and their members who are
' to receive automated telephone messages.

(snip)

'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=* =*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=* =*=*=*=*

Dim strGrpIDs() As String 'The corresponding group names are loaded into the array strColHdgs.
Dim iGroups As Integer 'Count of the number of groups found when Group information loaded.

Dim strColHdgs() As String
Dim strBCarray() As String 'This array will contain the rows and columns from which we'll load the cells in the XLS file
Dim iRow As Integer 'Row index for the BCarray
Dim jCol As Integer 'Column index for the BCarray
Dim XLSName As String 'Name of the Excel file.
Dim grpTxtFile As String
Dim strTxtEditor As String


I will add the ", 56" and post back.

[GraeagleBill]

The use of the added ", 56" is not a supported method in Access 2003.

[June7]

Rats! That worked so nicely in Access2007. Sorry, I no longer have Access2003 available. You might be stuck with your original process.

[GraeagleBill]

It might require the 2010 object library. When the 2010 environment becomes available, I'll compile the app and see if I get different results.
Thanks for your thoughts.
Bill

[GraeagleBill]

Hello June7,
What does the parameter "56" signify to the oBook.SaveAs command?
Bill

[June7]

I only know what I read in the link I referenced: file format

[GraeagleBill]

Got it!

Thanks,
Bill