[diane[_2_]]

Our department would like to download some G/L balances
for companies that are members of six specific groups.
The groups are not identified in our database. Our IS
department told me that the only way it could be done,
based on the way the database was structured, was to
programmatically query each company for the data.

I set up the following code by first recording a macro to
query the database for a specific account balance for a
specific company and then modified it slightly. The
companies for each group are listed under ranges named by
their group name. I have a procedure that runs the
following query for each property on the each list range
and places the results in a newly created workbook on a
sheet that corresponds to the group name so the info can
be saved and refreshed at a later date (eventually want to
set up date filter and account number as a variable if I
can get this to work).

A number of the companies are queried successfully, but
then there is an Excel.exe error and Excel closes before
all the queries for all properties in the lists are
completed. The error is sporadic, not usually happening
on the same property. This may be the wrong way to go
about accomplishing this task, but I am not getting too
much assistance or direction from our IS department. I’m
not going to even suggest that I totally know what I’m
doing, but I wanted to make an attempt to produce this
information. I don’t know if it is a problem with the
code, or if I pushing the limits of Excel and query
tables, or if it is a workstation problem (i.e. memory
issue).

Here is the code I am using (I am using querytable because
I am not sure how to do it another way. Can someone help
me out, please:

(qWkbk1 is previously set to Thisworkbook and
newWkbk set to newly created workbook)

Sub Get_Balance()

Dim c As Range
Dim rngCompany As Range
Dim cName As String
Dim datarow As Long
Dim datacol as long
Dim rngNames()

rngNames = Array("Group1", "Group2", "Group3", _
"Group4", "Group5", "Group6")

'Used to Identify List and Range

For anum = 0 To UBound(rngNames)

'Sets list range

Set rngCompany = qWkbk1 _
.Worksheets("GroupList") _
.Range(rngNames(anum))

rwcount = rngCompany.Rows.Count
colcount = rngCompany.Columns.Count

'resize list range to include company names only

Set rngCompany = rngCompany _
..Resize(rwcount - 1, colcount - 0)

'Sets beginning row and column for destination sheet

datarow = 6
datacol = 1

For Each c In rngCompany

cName = c.Value

'add sheet in new workbook if not yet created

With newWkbk
If ActiveSheet.Name < rngNames(anum) Then
.Sheets.Add.Name = rngNames(anum)
Else
End If
End With

'query for company balance

With newWkbk.Worksheets(rngNames(anum)).QueryTables _
.Add(Connection:="ODBC;DSN=MyDSN;CSF=Yes;" _
& "SName=222.333.4.5;NType=tcp;" _
& "UID=user1;PWD=1;CN=" & cName & ";", _
Destination:=newWkbk.Worksheets(rngNames(anum)).Ce lls
(datarow, datacol))
.CommandText = _
"SELECT ""Company Information"".Name,""Company
Information"".""Property #""," _
& """G/L Account"".No_, ""G/L Account"".Name, ""G/L
Account"".""Balance at Date""" _
& Chr(13) & "" & Chr(10) & "FROM ""Company
Information"" ""Company Information""," _
& """G/L Account"" ""G/L Account""" & Chr(13) & "" & Chr
(10) _
& "WHERE (""G/L Account"".""Date Filter""='06/25/03')
AND (""G/L Account"".No_='1111-111111')"

.Name = cName
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

datarow = datarow + 1
Application.StatusBar = cName

Next c

Next anum

End Sub

Thank you.

Diane

Windows 2000; Excel 2000

[Tom Ogilvy]

Change
.BackgroundQuery = True

to
.BackgroundQuery = False

and see how it runs. This will cause the queries to be performed
sequentially.

Regards,
Tom Ogilvy


diane wrote in message
...
Our department would like to download some G/L balances
for companies that are members of six specific groups.
The groups are not identified in our database. Our IS
department told me that the only way it could be done,
based on the way the database was structured, was to
programmatically query each company for the data.

I set up the following code by first recording a macro to
query the database for a specific account balance for a
specific company and then modified it slightly. The
companies for each group are listed under ranges named by
their group name. I have a procedure that runs the
following query for each property on the each list range
and places the results in a newly created workbook on a
sheet that corresponds to the group name so the info can
be saved and refreshed at a later date (eventually want to
set up date filter and account number as a variable if I
can get this to work).

A number of the companies are queried successfully, but
then there is an Excel.exe error and Excel closes before
all the queries for all properties in the lists are
completed. The error is sporadic, not usually happening
on the same property. This may be the wrong way to go
about accomplishing this task, but I am not getting too
much assistance or direction from our IS department. I’m
not going to even suggest that I totally know what I’m
doing, but I wanted to make an attempt to produce this
information. I don’t know if it is a problem with the
code, or if I pushing the limits of Excel and query
tables, or if it is a workstation problem (i.e. memory
issue).

Here is the code I am using (I am using querytable because
I am not sure how to do it another way. Can someone help
me out, please:

(qWkbk1 is previously set to Thisworkbook and
newWkbk set to newly created workbook)

Sub Get_Balance()

Dim c As Range
Dim rngCompany As Range
Dim cName As String
Dim datarow As Long
Dim datacol as long
Dim rngNames()

rngNames = Array("Group1", "Group2", "Group3", _
"Group4", "Group5", "Group6")

'Used to Identify List and Range

For anum = 0 To UBound(rngNames)

'Sets list range

Set rngCompany = qWkbk1 _
.Worksheets("GroupList") _
.Range(rngNames(anum))

rwcount = rngCompany.Rows.Count
colcount = rngCompany.Columns.Count

'resize list range to include company names only

Set rngCompany = rngCompany _
.Resize(rwcount - 1, colcount - 0)

'Sets beginning row and column for destination sheet

datarow = 6
datacol = 1

For Each c In rngCompany

cName = c.Value

'add sheet in new workbook if not yet created

With newWkbk
If ActiveSheet.Name < rngNames(anum) Then
.Sheets.Add.Name = rngNames(anum)
Else
End If
End With

'query for company balance

With newWkbk.Worksheets(rngNames(anum)).QueryTables _
.Add(Connection:="ODBC;DSN=MyDSN;CSF=Yes;" _
& "SName=222.333.4.5;NType=tcp;" _
& "UID=user1;PWD=1;CN=" & cName & ";", _
Destination:=newWkbk.Worksheets(rngNames(anum)).Ce lls
(datarow, datacol))
.CommandText = _
"SELECT ""Company Information"".Name,""Company
Information"".""Property #""," _
& """G/L Account"".No_, ""G/L Account"".Name, ""G/L
Account"".""Balance at Date""" _
& Chr(13) & "" & Chr(10) & "FROM ""Company
Information"" ""Company Information""," _
& """G/L Account"" ""G/L Account""" & Chr(13) & "" & Chr
(10) _
& "WHERE (""G/L Account"".""Date Filter""='06/25/03')
AND (""G/L Account"".No_='1111-111111')"

.Name = cName
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

datarow = datarow + 1
Application.StatusBar = cName

Next c

Next anum

End Sub

Thank you.

Diane

Windows 2000; Excel 2000

[diane[_2_]]

Thank you, Tom, for responding.

I made the changes as you suggested. Seemed to get a few
more queries completed, but, alas, I still got the
excel.exe error.

I checked error log, which indicated exception error
c0000005. I did search on MS Knowledgebase and found that
similar errors were corrected with Windows 2000 service
pack 3. I have SP2. Went to a workstation with SP3 and
it worked on about 5 out of 6 attempts (one attempt did
generate the excel.exe error). I am going to make request
to IS dept to upgrade w/SP3 and see if that makes a
difference on my workstation and go from there. I may be
reposting at a later date.

Thanks, again!

diane


-----Original Message-----
Change
.BackgroundQuery = True

to
.BackgroundQuery = False

and see how it runs. This will cause the queries to be
performed
sequentially.

Regards,
Tom Ogilvy