[AccessD] Mixed Versions

[Rocky Smolin]

Dan:

I'm not using TransferSpreadsheet.  I'm using VBA to output the data cell by
cell.  Here's a formatting snip:

    Set objXLApp = CreateObject("Excel.Application")
    objXLApp.Workbooks.Open Me.txtOutputFileName
    
    If Me.fraTemplate = 3 Then GoTo Alcoa:

    Set objXLWS = objXLApp.ActiveSheet
    
    ' do some formatting
    If Me.fraTemplate <> 4 Then
        objXLWS.PageSetup.PrintGridlines = True
        objXLWS.PageSetup.Orientation = xlPortrait
        objXLApp.Rows("1:1").Select
        objXLApp.Selection.HorizontalAlignment = xlCenter
        objXLApp.Columns("A:K").Select
        objXLApp.Selection.HorizontalAlignment = xlCenter
        objXLApp.Selection.WrapText = True
        objXLApp.Selection.Font.Bold = True
        
        objXLApp.Columns("A:A").ColumnWidth = 11
        objXLApp.Columns("B:B").ColumnWidth = 9.71
        objXLApp.Columns("C:C").ColumnWidth = 6.43
        objXLApp.Columns("D:D").ColumnWidth = 15
        objXLApp.Columns("E:E").ColumnWidth = 10.29
        objXLApp.Columns("F:F").ColumnWidth = 10.43
        objXLApp.Columns("G:G").ColumnWidth = 11
        objXLApp.Columns("H:H").ColumnWidth = 9.71
        objXLApp.Columns("H:H").ColumnWidth = 10.71
        objXLApp.Columns("I:I").ColumnWidth = 6.43
        objXLApp.Columns("J:J").ColumnWidth = 8.14
        objXLApp.Columns("K:K").ColumnWidth = 9.57
    
    End If

Here's a data transfer snip:

            Do While Not rs.EOF
                objXLWS.Cells(intRow, 1) = rs!fldContainerRecordingDate
                objXLWS.Cells(intRow, 2) = rs!fldContainerCheck4
                objXLWS.Cells(intRow, 3) = rs!fldContainerCheck5
                objXLWS.Cells(intRow, 4) = rs!fldLotNumber
                objXLWS.Cells(intRow, 5) = rs!fldContainerRackLetter
                objXLWS.Cells(intRow, 6) = rs!fldJobsJobNumber
                objXLWS.Cells(intRow, 8) = _
                    (rs!fldContainerGross - rs!fldContainerTare) /
objXLWS.Cells(2, 8)
                objXLWS.Cells(intRow, 9) = rs!fldContainerTotalPieces
                objXLWS.Cells(intRow, 10) = rs!fldContainerGross -
rs!fldContainerTare
                objXLWS.Cells(intRow, 11) = rs!fldContainerTare
                objXLWS.Cells(intRow, 12) = rs!fldContainerGross
                objXLWS.Cells(intRow, 15) = rs!fldTruckPlanShipDate
                intRow = intRow + 1
                rs.MoveNext

I like this method - you have real precise control over the spreadsheet.
Unfortunately the line of code with the problem ocurrs before any of these
machinations. 

R

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan
Waters
Sent: Monday, August 03, 2015 2:28 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Mixed Versions

Hi Rocky,

When you use the TransferSpreadsheet function, you use something like do
this:

        DoCmd.TranserSpreadsheet acExport, acSpreadsheetType,
"tableORquery", "FullPathToSpreadsheetFile"

acSpreadsheetType for Access 2000, 2002, 2003, and 2007 is
acSpreadsheetTypeExcel9 (or just 9).

acSpreadsheetType for Access 2010 and up is acSpreadsheetTypeExcel12 (or
just 10).

Check to see if you are using acSpreadsheetTypeExcel12 (or 10).

Another way I create a spreadsheet from Access data is this:
        DoCmd.OutputTo acOutputQuery, stgSQL, acFormatXLS,
stgSpreadsheetFullPath I think this is a 'generic' way of moving the data,
but this has sometimes worked for me when TransferSpreadsheet didn't work.

Good Luck!
Dan

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Rocky Smolin
Sent: Sunday, August 02, 2015 3:10 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Mixed Versions

Dear List:
 
I created an app for a client in A2003 that exports stuff to Excel but using
VBA not the Transfer Spreadsheet function.  I used early binding because
that seems to work - even when the target machine has Office 2007 or 2010 -
the reference seems to resolve OK to the version excel on that machine.
 
However, the client recently upgraded to O2013 but without Access.  So
they're using Excel 2013 with Access 2003.  And of course, the export broke.
I thought that the solution would be to switch to late binding. Which works
on my O2003 machine fine.  But still does not work on the clients mixed
version machine.
 
It blows up on the line of code (but unfortunately I cannot find the email
that has the error message):
 
    objXLApp.Workbooks.Open Me.txtOutputFileName

The objects are DIMmed:
 
Dim objXLApp As Object
Dim objXLBook As Object
Dim objXLWS As Object

and the code snip is:
 
    Call CopyFileA(Me.txtRecievingInspectionCutStockTemplate,
Me.txtOutputFileName, False)

    Set objXLApp = CreateObject("Excel.Application")
    objXLApp.Workbooks.Open Me.txtOutputFileName
    Set objXLWS = objXLApp.ActiveSheet

where txtOutputFileName has the path and file name to be opened.
 
I'll try to get some more info but in the meantime is there some change I
have to make to syntax in  objXLApp.Workbooks.Open for Excel 2013? 
 
MTIA
 
Rocky
 
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com