|
Macro freezes up Excel
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 |
Macro freezes up Excel
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" wrote in message ... 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 |
Macro freezes up Excel
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" wrote in message ... 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 |
Macro freezes up Excel
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" wrote in message ... 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" wrote in message ... 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 |
| All times are GMT +1. The time now is 09:52 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com