Scrambled Excel Automation

[acuttitta]

I ran into something really bizzaro today.

A team member had me look into some export code for some data he was
outputting. The data looks at shipping containers and attempts to look
at how often their being reused. Details are output according to the
maximum number of times the same container was used, up to a total of
six moves.

The code we're executing is so base-simple that it's second nature to
us. We have a select query that formats the data for output, and has a
static variable that corresponds to the maximum times the container was
used. Then, through code (see loop snippet at end of post), we loop
through the potential "max moves", and export the "format" query all
into the same workbook.

The bizzaro thing: As often as we've done this in the past (and our
users have similar routines running all day long), this is COMPLETELY
scrambling the data with each pass.

Pass 1: "Moves1" looks fine.
Pass 2: "Moves2" looks fine, but "Moves1" has some orders jumbled.
Pass 3: "Moves3" looks fine, "Moves2" has some orders jumbled,
"Moves1" is even more jumbled.
Pass 4: "Moves4" looks fine, "Moves3" has some orders jumbled,
"Moves2" is even more jumbled, and "Moves 1" is barely recognizable.

By the time pass 6 six hits, there are dozens of duplications in
Moves1. Data from completely different columns are moved around. The
original column headings are obliterated.

The code "ExportToXLS" basically runs DoCmd.TransferSpreadsheet, and
does some bolding, but does NO manipulation of actual data.

The only thing I can think of us the monstrosity of data in this
output. "Moves1" is 38K records, then 12K, 4K, 1K, 400, and "Moves6"
has 100 records.

Has anyone else encountered something like this? Insight appreciated.

Thanks,
Anthony.
For intX = 1 To 6
Call StaticInteger(intX)
strTempQuery = "Moves" & intX
' Delete the Temp Query (in case it didn't get deleted last time)
Call KillObject(strTempQuery, acQuery)
strSQL = "SELECT [BRT - qryTemp].* FROM [BRT - qryTemp];"
Set qdfBRT = dbBRT.CreateQueryDef(strTempQuery, strSQL)
Call ExportToXLS(strTempQuery, strOutputPath & strOutputFile)
'Cleanup - kill the Temp Query
Call KillObject(strTempQuery, acQuery)
Next intX

[Tom van Stiphout]

On 17 Feb 2005 18:23:22 -0800, "ac*******@railvan.com"
<ac*******@railvan.com> wrote:

Your code looks clean enough.
One thing I might add is:
dbBRT.Querydefs.Refresh
after the KillObject call.
Another thing: you are creating a QueryDef object, and not cleaning it
up. Add:
qdfBRT.Close
set qdfBRT = Nothing

-Tom.
<clip>

For intX = 1 To 6
Call StaticInteger(intX)
strTempQuery = "Moves" & intX
' Delete the Temp Query (in case it didn't get deleted last time)
Call KillObject(strTempQuery, acQuery)
strSQL = "SELECT [BRT - qryTemp].* FROM [BRT - qryTemp];"
Set qdfBRT = dbBRT.CreateQueryDef(strTempQuery, strSQL)
Call ExportToXLS(strTempQuery, strOutputPath & strOutputFile)
'Cleanup - kill the Temp Query
Call KillObject(strTempQuery, acQuery)
Next intX

[acuttitta]

Inside of the code that runs KillObject, the QueryDef.Refresh occurs.

The qdf.close isn't there, but the = Nothing happens in the exit
section of the code.

Still doesn't explain the transpositioning of the data.

[Rich P]

If you are using TransferSpreadsheet in your code, then that is where
your problem is. My experience with TransferSpreadsheet is that it
works reliably only once on a NEW workbook. If you use it again and
again on the same workbook or in a loop, the results become
unpredictable. Anyway, that has been my experience.

My workaround has been to use Com ADO to write data to Excel from
Access. You can use DAO with DBEngine, but for me, the easiest way was
ADO (make a reference to Microsoft ActiveX Data Objects Library 2.5 or
2.6).

Here is a sample:

---------------------------------------------------
Sub WriteDataToExcelWithADO()
Dim cn As New ADODB.Connection, RS As New ADODB.Recordset
Dim i As Integer, DB As Database, RS1 As DAO.Recordset

Set DB = CurrentDb

strSourcePath = Left(DB.Name, Len(DB.Name) - Len(Dir(DB.Name)))
'---note: need semicolon at end of .xls; for ADO to Excel
strSourcePath = strSourcePath & "TestWorkBook.xls;"
RS.CursorLocation = adUseClient
cn.Mode = adModeReadWrite
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strSourcePath & _
"Extended Properties=""Excel 8.0;HDR=NO;"""

Set RS1 = DB.OpenRecordset("tbl1")

j = 2
DoEvents
Do While Not RS1.EOF
strSql = "SELECT * FROM [Sheet1$A" & j & ":U" & j & "]"
RS.Open strSql, cn, adOpenDynamic, adLockPessimistic
For i = 0 To RS1.Fields.Count - 1
RS(k) = RS1(k)
Next
RS.Update
RS.Close
j = j + 1
RS1.MoveNext
Loop
RS1.Close
cn.Close
End Sub

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

Here are some caveats. You have to prep the Excel workbook. Unlike
TransferSpreadsheet, you can rewrite and also write to any cell in the
Spreadsheet. But you have to plant some fake data in the spreadsheet
cells you need to write to like this:

ColA ColB ColC ColD
Row1 sdfs sdf sdf sdf
Row2 sdf sdf sdf sdf
Row3 sdf sdf sdf sdf

having the fake data prevents an apostrophe from appearing in front of
the data

ColA ColB ColC ColD
Row1 '1 '3 '34 '5
Row2 '45 '67 '5 '9
Row3 '0 '90 '36 '78

If you don't prep with the fake data and you try to sum a column with
the data you just passed in, it won't work.

Also, ADO only writes one row at a time. So you select one row in your
range at a time
j = 2
strSql = "SELECT * FROM [Sheet1$A" & j & ":U" & j & "]"

j is the row counter. Here the range is A2:U2

You have to close your ADO recordset, RS.Close after writing a row of
data. Then MoveNext for RS1 (dao recordset) and increment j (j = 3,
etc).

If this seems tedious and not worth the work, I have dozens of programs
that write thousands of rows of data from Access to dozens of Excel
workbooks on a daily basis. These programs run continuously 7 days a
week hands off/unattended. Com ADO has been serving me reliably for
over 5 years with this method. So I just thought I would share.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!