Macro fails when not performing "step into"
 

Hello, I am rather new to excel, but was required to develope a script
for work. This script is designed to read data from a Citadel database
for Lookout (SCADA software), add the current runtime information to a
value stored in an MS SQL database, and insert it into the MS SQL
database. This needed to be done because the Citadel database drops the
data after 1 year, so equipment runtime can not be tracked.

I developed 3 scripts, 1 for reading the data from the MSSQL server
(like the last time script was run), next data is read from the Citadel
DB, and the last script is for writing the data back into MSSQL.

The 3 scripts do what they are supposed to when run seperatly by hand,
but we need to combine these into 1 script that will automatically run.
When I merged the scripts, they no longer worked. If I run the combined
macro with the "Step Into" line by line, it works. :confused:

Does excel not insert data acquired from a database until the macro is
done executing, which would explain why the next steps can not use the
data?

Here is the long, poorly written mess I call my script:

Sub Doall()

Dim lRealLastRow As Long
Range("A1").Select
On Error Resume Next
lRealLastRow = Cells.Find("*", Range("A1"), xlFormulas, , xlByRows,
_
xlPrevious).Row

' --- Readcmms ---

For countnum = 1 To lRealLastRow
'countnum = "4"

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=SQLOLEDB.1;Password=****;Persi st Security
Info=True;User ID=bob;Initial Catalog=entWORK;Data Source=SQLSERVER;Use
Pr" _
, _
"ocedure for Prepare=1;Auto Translate=True;Packet
Size=4096;Workstation ID=PC2400-1003;Use Encryption for Data=False;Tag
with col" _
, "umn collation when possible=False"), Destination:=Range("D"
& countnum))
..CommandType = xlCmdSql
..CommandText = Array( _
"SELECT UDFChar5 FROM Asset WHERE ""AssetID"" = '" &
ActiveSheet.Range("A" & countnum) & "'; UPDATE ""dbo"".""Asset"" SET
""UDFChar5"" = '" & Now() & "'WHERE ""AssetID"" = '" &
ActiveSheet.Range("A" & countnum) & "'" _
)
..Name = "SQLSERVER entWORK_1"
..FieldNames = False
..RowNumbers = False
..FillAdjacentFormulas = False
..PreserveFormatting = True
..RefreshOnFileOpen = False
..BackgroundQuery = False
..RefreshStyle = xlOverwriteCells
..SavePassword = True
..SaveData = True
..AdjustColumnWidth = True
..RefreshPeriod = 0
..PreserveColumnInfo = True
..SourceConnectionFile = _
"C:\Documents and Settings\bob\My Documents\My Data
Sources\SQLSERVER entWORK.odc"
..Refresh BackgroundQuery = False
' ActiveSheet.Range("C1") = Now()
End With

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=SQLOLEDB.1;Password=****;Persi st Security
Info=True;User ID=bob;Initial Catalog=entWORK;Data Source=SQLSERVER;Use
Pr" _
, _
"ocedure for Prepare=1;Auto Translate=True;Packet
Size=4096;Workstation ID=PC2400-1003;Use Encryption for Data=False;Tag
with col" _
, "umn collation when possible=False"), Destination:=Range("E"
& countnum))
..CommandType = xlCmdSql
..CommandText = Array( _
"SELECT Meter1Reading FROM Asset WHERE ""AssetID"" = '" &
ActiveSheet.Range("A" & countnum) & "';" _
)
..Name = "SQLSERVER entWORK_1"
..FieldNames = False
..RowNumbers = False
..FillAdjacentFormulas = False
..PreserveFormatting = True
..RefreshOnFileOpen = False
..BackgroundQuery = False
..RefreshStyle = xlOverwriteCells
..SavePassword = True
..SaveData = True
..AdjustColumnWidth = True
..RefreshPeriod = 0
..PreserveColumnInfo = True
..SourceConnectionFile = _
"C:\Documents and Settings\bob\My Documents\My Data
Sources\SQLSERVER entWORK.odc"
..Refresh BackgroundQuery = False
ActiveSheet.Range("C" & countnum) = Now()
End With

Next countnum

* MORE CODE (~150 lines)... cut out to keep post short. The queries are
mostly the same, just different CommandText. If rest would help reply
so*

End Sub


--
shrek
------------------------------------------------------------------------
shrek's Profile: http://www.excelforum.com/member.php...o&userid=26047
View this thread: http://www.excelforum.com/showthread...hreadid=393932