Macro freezes up Excel

Hello

I have a small worksheet, it goes out to a database and gets some counts.
Then cell by cell, it populates the 1 sheet in the file.

There are maybe 100 rows being created.

On one machine, it locks up excel. Every other machine runs fine.
The machine that locks up is running XP with all updates, has 1GIG RAM and
like 10gig free of HD space. Office 2002.

These machines are pretty much the same configuration.

Any ideas ?

Thanks
Jon

[email protected]

Would need to know more about the code and about how far it gets before it
locks up, but the first thing that comes to mind is accessing the database:
are you sure that the machine that has problems has a good connection to your
database? Where is it (local directory, network) and how are you accessing
it (MSQuery? Embedded link? VBA code?). Are you sure that all of this is
set up properly on the machine in question? If it can't find the database it
might "freeze" while trying to make the connection.
--
- K Dales


"Jon Delano" wrote:

> Hello
>
> I have a small worksheet, it goes out to a database and gets some counts.
> Then cell by cell, it populates the 1 sheet in the file.
>
> There are maybe 100 rows being created.
>
> On one machine, it locks up excel. Every other machine runs fine.
> The machine that locks up is running XP with all updates, has 1GIG RAM and
> like 10gig free of HD space. Office 2002.
>
> These machines are pretty much the same configuration.
>
> Any ideas ?
>
> Thanks
> Jon
>
> [email protected]
>
>
>

If the code used to run OK then it pretty well narrows it down to an issue
with the database connection. Do you get any output at all before it
freezes? Have you tried stepping through the code?

I would recommend you do that - step through the code. You should then be
able to at least find where it is freezing. The most critical line to check
will be where you execute the .Open method to make the connection - if it
freezes there it is obviously having problems communicating with the
database. You can try setting the Connection.ConnectionTimeout property to
limit the time allowed so at least you can continue to run and check your
code if the connection fails.

If it makes it through there OK, then the recordset .Open is the next
possible problem. Normally it would return an error if it can't execute, but
if it freezes it could be because the query is taking forever to run. A way
to limit this might be to set the MaxRecords property to something low (maybe
100 records) to see if you are getting any records at all. I would also
suggest printing the complete text of the SQL command string (? SQL in the
immediate pane just before you run the rs.Open) and examine it carefully.

It is hard to troubleshoot without being hands on, but I would focus on
these issues. And, though the client says nothing has changed, I would see
if any of the following may have happened:
1) Updates to any software involved (including installation of any new
service packs for MSOffice)
2) Windows updates
3) subtle changes in the Excel file - like cells used to build your SQL that
may have been reformatted from numeric to text.

Also, check the ADO reference library files (go to the VBA editor references
and look at the Microsoft ActiveX Data Objects reference - check the version
number and the file name/location/date) for any discrepancies between the
machines that work and the one that doesn't.

Can't do much more without being there for hands-on testing, but hope these
suggestions help you get to the root of the problem.
--
- K Dales


"Jon Delano" wrote:

> Hi
>
> Thanks for the quick reply.
> The database is an SQL Server on the local network. The macro used to run
> just fine on this machine. So the setup to access the data should be just
> fine.
>
> This is a client and of course they say nothing has changed ... but we all
> know how that goes.
>
> Here is the macro that runs when the user clicks the button .. which
> currently fills 82 rows of data.
>
> Thanks again.
> Jon
>
>
>
> Private Sub btnUpdateCounts_Click()
>
> Dim SQL As String
> Dim cn As New ADODB.Connection
> Dim rs As New ADODB.Recordset
> Dim NewDate As Boolean
>
> With cn
> .Open ("Provider=SQLOLEDB;data source=webserver;initial
> catalog=OneDayAcuvue;user id=sa;pwd=**********")
> End With
>
> ' count emails by date by type
> SQL = "select DateCreated, Type, count(TransactionID) as NumEmails from
> [Transaction] " & _
> "where id > 0 group by datecreated, type order by datecreated
> desc, type"
>
> rs.Open SQL, cn, adOpenForwardOnly, adLockReadOnly
>
> Dim CellRow As Integer
> Dim ColumnNo As Integer
> Dim CheckDate As String
> CellRow = 9
>
> NewDate = True
> Do While Not rs.EOF
> CheckDate = Format(rs("DateCreated"), "mm/dd/yy")
> Sheet1.Cells(CellRow, 1) = Format(rs("DateCreated"), "mm/dd/yy")
>
> If NewDate Then
> Sheet1.Cells(CellRow, 2) = ""
> Sheet1.Cells(CellRow, 3) = ""
> Sheet1.Cells(CellRow, 4) = ""
> Sheet1.Cells(CellRow, 5) = ""
> Sheet1.Cells(CellRow, 6) = ""
> End If
>
> Select Case rs("Type")
> Case "X3"
> ColumnNo = 2
> Case "X4"
> ColumnNo = 3
> Case "X4"
> ColumnNo = 4
> Case "Y2"
> ColumnNo = 5
> Case "Y3"
> ColumnNo = 6
> End Select
>
> Sheet1.Cells(CellRow, ColumnNo) = Sheet1.Cells(CellRow, ColumnNo) +
> rs("NumEmails")
> rs.MoveNext
> If Not rs.EOF Then
> If CheckDate <> Format(rs("DateCreated"), "mm/dd/yy") Then
> CellRow = CellRow + 1
> NewDate = True
> Else
> CheckDate = Format(rs("DateCreated"), "mm/dd/yy")
> NewDate = False
> End If
> End If
> Loop
>
> rs.Close
>
> ' count registrations by date, type
> SQL = "select cast(cast(datepart(""MM"", DateCreated) as varchar) + '/'
> + cast(datepart(""DD"", DateCreated) as varchar) + '/' + " & _
> "cast(datepart(""YY"", DateCreated) as varchar) as datetime) as
> cDate, count(CustomerID) as CountReg, RecordType " & _
> "from Customer " & _
> "group by cast(cast(datepart(""MM"", DateCreated) as varchar) +
> '/' + cast(datepart(""DD"", DateCreated) as varchar) + '/' + " & _
> "cast(datepart(""YY"", DateCreated) as varchar) as datetime),
> RecordType " & _
> "order by cDate desc, RecordType "
>
> rs.Open SQL, cn, adOpenForwardOnly, adLockReadOnly
>
> CellRow = 9
>
> NewDate = True
> Do While Not rs.EOF
> CheckDate = Format(rs("cDate"), "mm/dd/yy")
> Sheet1.Cells(CellRow, 9) = Format(rs("cDate"), "mm/dd/yy")
>
> If NewDate Then
> Sheet1.Cells(CellRow, 10) = ""
> Sheet1.Cells(CellRow, 11) = ""
> End If
>
> Select Case rs("RecordType")
> Case 1
> ColumnNo = 10
> Case 2
> ColumnNo = 11
> End Select
>
> Sheet1.Cells(CellRow, ColumnNo) = rs("CountReg")
> rs.MoveNext
> If Not rs.EOF Then
> If CheckDate <> Format(rs("cDate"), "mm/dd/yy") Then
> CellRow = CellRow + 1
> NewDate = True
> Else
> CheckDate = Format(rs("cDate"), "mm/dd/yy")
> NewDate = False
> End If
> End If
> Loop
>
> rs.Close
>
> ' count proof of purchase entries entered by geoDirect
> SQL = "select cast(cast(datepart(""MM"", POPEnterDate) as varchar) + '/'
> + cast(datepart(""DD"", POPEnterDate) as varchar) + '/' + " & _
> "cast(datepart(""YY"", POPEnterDate) as varchar) as datetime) as
> cDate, count(CustomerID) as CountReg, RecordType " & _
> "from Customer " & _
> "where ProofOfPurchase=1 " & _
> "group by cast(cast(datepart(""MM"", POPEnterDate) as varchar) +
> '/' + cast(datepart(""DD"", POPEnterDate) as varchar) + '/' + " & _
> "cast(datepart(""YY"", POPEnterDate) as varchar) as datetime),
> RecordType " & _
> "order by cDate desc, RecordType "
>
> rs.Open SQL, cn, adOpenForwardOnly, adLockReadOnly
>
> CellRow = 9
>
> NewDate = True
> Do While Not rs.EOF
> CheckDate = Format(rs("cDate"), "mm/dd/yy")
> Sheet1.Cells(CellRow, 14) = Format(rs("cDate"), "mm/dd/yy")
>
> If NewDate Then
> Sheet1.Cells(CellRow, 15) = ""
> Sheet1.Cells(CellRow, 16) = ""
> End If
>
> Select Case rs("RecordType")
> Case 1
> ColumnNo = 15
> Case 2
> ColumnNo = 16
> End Select
>
> Sheet1.Cells(CellRow, ColumnNo) = rs("CountReg")
> rs.MoveNext
>
> If Not rs.EOF Then
> If CheckDate <> Format(rs("cDate"), "mm/dd/yy") Then
> CellRow = CellRow + 1
> NewDate = True
> Else
> CheckDate = Format(rs("cDate"), "mm/dd/yy")
> NewDate = False
> End If
> End If
> Loop
>
> rs.Close
>
> cn.Close
>
> Set rs = Nothing
> Set cn = Nothing
>
> End Sub
>
>
>
>
>
> "K Dales" <[email protected]> wrote in message
> news:[email protected]...
> > Would need to know more about the code and about how far it gets before it
> > locks up, but the first thing that comes to mind is accessing the
> > database:
> > are you sure that the machine that has problems has a good connection to
> > your
> > database? Where is it (local directory, network) and how are you
> > accessing
> > it (MSQuery? Embedded link? VBA code?). Are you sure that all of this is
> > set up properly on the machine in question? If it can't find the database
> > it
> > might "freeze" while trying to make the connection.
> > --
> > - K Dales
> >
> >
> > "Jon Delano" wrote:
> >
> >> Hello
> >>
> >> I have a small worksheet, it goes out to a database and gets some counts.
> >> Then cell by cell, it populates the 1 sheet in the file.
> >>
> >> There are maybe 100 rows being created.
> >>
> >> On one machine, it locks up excel. Every other machine runs fine.
> >> The machine that locks up is running XP with all updates, has 1GIG RAM
> >> and
> >> like 10gig free of HD space. Office 2002.
> >>
> >> These machines are pretty much the same configuration.
> >>
> >> Any ideas ?
> >>
> >> Thanks
> >> Jon
> >>
> >> [email protected]
> >>
> >>
> >>
>
>
>

Thanks for all the good info.

I went out to the client site today .. as soon as the user opened the file
it was crashing excel. So much for the lockup.
So I just created a new file from scratch ... copied and pasted the macro
and the headings .. tested and it worked fine for them

Now as to why the old file worked on all machines but the one, I have no
idea .. but they can all use this new file.

Thanks again for your help.
Jon


"K Dales" <[email protected]> wrote in message
news:[email protected]...
> If the code used to run OK then it pretty well narrows it down to an issue
> with the database connection. Do you get any output at all before it
> freezes? Have you tried stepping through the code?
>
> I would recommend you do that - step through the code. You should then be
> able to at least find where it is freezing. The most critical line to
> check
> will be where you execute the .Open method to make the connection - if it
> freezes there it is obviously having problems communicating with the
> database. You can try setting the Connection.ConnectionTimeout property
> to
> limit the time allowed so at least you can continue to run and check your
> code if the connection fails.
>
> If it makes it through there OK, then the recordset .Open is the next
> possible problem. Normally it would return an error if it can't execute,
> but
> if it freezes it could be because the query is taking forever to run. A
> way
> to limit this might be to set the MaxRecords property to something low
> (maybe
> 100 records) to see if you are getting any records at all. I would also
> suggest printing the complete text of the SQL command string (? SQL in the
> immediate pane just before you run the rs.Open) and examine it carefully.
>
> It is hard to troubleshoot without being hands on, but I would focus on
> these issues. And, though the client says nothing has changed, I would
> see
> if any of the following may have happened:
> 1) Updates to any software involved (including installation of any new
> service packs for MSOffice)
> 2) Windows updates
> 3) subtle changes in the Excel file - like cells used to build your SQL
> that
> may have been reformatted from numeric to text.
>
> Also, check the ADO reference library files (go to the VBA editor
> references
> and look at the Microsoft ActiveX Data Objects reference - check the
> version
> number and the file name/location/date) for any discrepancies between the
> machines that work and the one that doesn't.
>
> Can't do much more without being there for hands-on testing, but hope
> these
> suggestions help you get to the root of the problem.
> --
> - K Dales
>
>
> "Jon Delano" wrote:
>
>> Hi
>>
>> Thanks for the quick reply.
>> The database is an SQL Server on the local network. The macro used to run
>> just fine on this machine. So the setup to access the data should be just
>> fine.
>>
>> This is a client and of course they say nothing has changed ... but we
>> all
>> know how that goes.
>>
>> Here is the macro that runs when the user clicks the button .. which
>> currently fills 82 rows of data.
>>
>> Thanks again.
>> Jon
>>
>>
>>
>> Private Sub btnUpdateCounts_Click()
>>
>> Dim SQL As String
>> Dim cn As New ADODB.Connection
>> Dim rs As New ADODB.Recordset
>> Dim NewDate As Boolean
>>
>> With cn
>> .Open ("Provider=SQLOLEDB;data source=webserver;initial
>> catalog=OneDayAcuvue;user id=sa;pwd=**********")
>> End With
>>
>> ' count emails by date by type
>> SQL = "select DateCreated, Type, count(TransactionID) as NumEmails
>> from
>> [Transaction] " & _
>> "where id > 0 group by datecreated, type order by datecreated
>> desc, type"
>>
>> rs.Open SQL, cn, adOpenForwardOnly, adLockReadOnly
>>
>> Dim CellRow As Integer
>> Dim ColumnNo As Integer
>> Dim CheckDate As String
>> CellRow = 9
>>
>> NewDate = True
>> Do While Not rs.EOF
>> CheckDate = Format(rs("DateCreated"), "mm/dd/yy")
>> Sheet1.Cells(CellRow, 1) = Format(rs("DateCreated"), "mm/dd/yy")
>>
>> If NewDate Then
>> Sheet1.Cells(CellRow, 2) = ""
>> Sheet1.Cells(CellRow, 3) = ""
>> Sheet1.Cells(CellRow, 4) = ""
>> Sheet1.Cells(CellRow, 5) = ""
>> Sheet1.Cells(CellRow, 6) = ""
>> End If
>>
>> Select Case rs("Type")
>> Case "X3"
>> ColumnNo = 2
>> Case "X4"
>> ColumnNo = 3
>> Case "X4"
>> ColumnNo = 4
>> Case "Y2"
>> ColumnNo = 5
>> Case "Y3"
>> ColumnNo = 6
>> End Select
>>
>> Sheet1.Cells(CellRow, ColumnNo) = Sheet1.Cells(CellRow, ColumnNo)
>> +
>> rs("NumEmails")
>> rs.MoveNext
>> If Not rs.EOF Then
>> If CheckDate <> Format(rs("DateCreated"), "mm/dd/yy") Then
>> CellRow = CellRow + 1
>> NewDate = True
>> Else
>> CheckDate = Format(rs("DateCreated"), "mm/dd/yy")
>> NewDate = False
>> End If
>> End If
>> Loop
>>
>> rs.Close
>>
>> ' count registrations by date, type
>> SQL = "select cast(cast(datepart(""MM"", DateCreated) as varchar) +
>> '/'
>> + cast(datepart(""DD"", DateCreated) as varchar) + '/' + " & _
>> "cast(datepart(""YY"", DateCreated) as varchar) as datetime) as
>> cDate, count(CustomerID) as CountReg, RecordType " & _
>> "from Customer " & _
>> "group by cast(cast(datepart(""MM"", DateCreated) as varchar) +
>> '/' + cast(datepart(""DD"", DateCreated) as varchar) + '/' + " & _
>> "cast(datepart(""YY"", DateCreated) as varchar) as datetime),
>> RecordType " & _
>> "order by cDate desc, RecordType "
>>
>> rs.Open SQL, cn, adOpenForwardOnly, adLockReadOnly
>>
>> CellRow = 9
>>
>> NewDate = True
>> Do While Not rs.EOF
>> CheckDate = Format(rs("cDate"), "mm/dd/yy")
>> Sheet1.Cells(CellRow, 9) = Format(rs("cDate"), "mm/dd/yy")
>>
>> If NewDate Then
>> Sheet1.Cells(CellRow, 10) = ""
>> Sheet1.Cells(CellRow, 11) = ""
>> End If
>>
>> Select Case rs("RecordType")
>> Case 1
>> ColumnNo = 10
>> Case 2
>> ColumnNo = 11
>> End Select
>>
>> Sheet1.Cells(CellRow, ColumnNo) = rs("CountReg")
>> rs.MoveNext
>> If Not rs.EOF Then
>> If CheckDate <> Format(rs("cDate"), "mm/dd/yy") Then
>> CellRow = CellRow + 1
>> NewDate = True
>> Else
>> CheckDate = Format(rs("cDate"), "mm/dd/yy")
>> NewDate = False
>> End If
>> End If
>> Loop
>>
>> rs.Close
>>
>> ' count proof of purchase entries entered by geoDirect
>> SQL = "select cast(cast(datepart(""MM"", POPEnterDate) as varchar) +
>> '/'
>> + cast(datepart(""DD"", POPEnterDate) as varchar) + '/' + " & _
>> "cast(datepart(""YY"", POPEnterDate) as varchar) as datetime)
>> as
>> cDate, count(CustomerID) as CountReg, RecordType " & _
>> "from Customer " & _
>> "where ProofOfPurchase=1 " & _
>> "group by cast(cast(datepart(""MM"", POPEnterDate) as varchar)
>> +
>> '/' + cast(datepart(""DD"", POPEnterDate) as varchar) + '/' + " & _
>> "cast(datepart(""YY"", POPEnterDate) as varchar) as datetime),
>> RecordType " & _
>> "order by cDate desc, RecordType "
>>
>> rs.Open SQL, cn, adOpenForwardOnly, adLockReadOnly
>>
>> CellRow = 9
>>
>> NewDate = True
>> Do While Not rs.EOF
>> CheckDate = Format(rs("cDate"), "mm/dd/yy")
>> Sheet1.Cells(CellRow, 14) = Format(rs("cDate"), "mm/dd/yy")
>>
>> If NewDate Then
>> Sheet1.Cells(CellRow, 15) = ""
>> Sheet1.Cells(CellRow, 16) = ""
>> End If
>>
>> Select Case rs("RecordType")
>> Case 1
>> ColumnNo = 15
>> Case 2
>> ColumnNo = 16
>> End Select
>>
>> Sheet1.Cells(CellRow, ColumnNo) = rs("CountReg")
>> rs.MoveNext
>>
>> If Not rs.EOF Then
>> If CheckDate <> Format(rs("cDate"), "mm/dd/yy") Then
>> CellRow = CellRow + 1
>> NewDate = True
>> Else
>> CheckDate = Format(rs("cDate"), "mm/dd/yy")
>> NewDate = False
>> End If
>> End If
>> Loop
>>
>> rs.Close
>>
>> cn.Close
>>
>> Set rs = Nothing
>> Set cn = Nothing
>>
>> End Sub
>>
>>
>>
>>
>>
>> "K Dales" <[email protected]> wrote in message
>> news:[email protected]...
>> > Would need to know more about the code and about how far it gets before
>> > it
>> > locks up, but the first thing that comes to mind is accessing the
>> > database:
>> > are you sure that the machine that has problems has a good connection
>> > to
>> > your
>> > database? Where is it (local directory, network) and how are you
>> > accessing
>> > it (MSQuery? Embedded link? VBA code?). Are you sure that all of this
>> > is
>> > set up properly on the machine in question? If it can't find the
>> > database
>> > it
>> > might "freeze" while trying to make the connection.
>> > --
>> > - K Dales
>> >
>> >
>> > "Jon Delano" wrote:
>> >
>> >> Hello
>> >>
>> >> I have a small worksheet, it goes out to a database and gets some
>> >> counts.
>> >> Then cell by cell, it populates the 1 sheet in the file.
>> >>
>> >> There are maybe 100 rows being created.
>> >>
>> >> On one machine, it locks up excel. Every other machine runs fine.
>> >> The machine that locks up is running XP with all updates, has 1GIG RAM
>> >> and
>> >> like 10gig free of HD space. Office 2002.
>> >>
>> >> These machines are pretty much the same configuration.
>> >>
>> >> Any ideas ?
>> >>
>> >> Thanks
>> >> Jon
>> >>
>> >> [email protected]
>> >>
>> >>
>> >>
>>
>>
>>