D
diane
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)).Cells
(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
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)).Cells
(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