Excel and Visual Basic
 

I have an excel 2007 spreadsheet that uses a Visual basic and sql to
populate. The procedure runs fine on all machines except two
machines. The program passes through one parameter and then clears
the work sheet and populates. The problem seems to be when the
worksheet is cleared. It just gets stuck

Sub ClearWorksheet()
Sheet1.Activate
Sheet1.Range("a5:az65000").Select
Selection.ClearContents
End Sub

If we change the above to only do this

Sub ClearWorksheet()
Sheet1.Activate
Sheet1.Range("a5:az1000").Select
Selection.ClearContents
End Sub

it runs but we need it to clear all 65000 rows or whatever the amount
might be.

Any body have any thoughts?

Here is the rest of the code:

Private Sub refreshbutton_Click()

Dim adoConnection As ADODB.Connection
Dim adoCMD As ADODB.Command
Dim adoRS As ADODB.Recordset
Dim adoPRM(1) As ADODB.Parameter
Dim strRange As String
Dim intRows As Integer


UserForm1.Show vbModal

If Not (UserForm1.blnContinue) Then
Unload UserForm1
Exit Sub
End If




ClearWorksheet

Set adoConnection = New ADODB.Connection
adoConnection.CursorLocation = adUseClient
adoConnection.ConnectionString =
"UID=;PWD=;DSN=SQL_server;DATABASE=MERION"
adoConnection.Open

Set adoCMD = New ADODB.Command
adoCMD.ActiveConnection = adoConnection
adoCMD.CommandType = adCmdStoredProc
adoCMD.CommandText = "XLS_rpt_SalesAnalysis"
adoCMD.CommandTimeout = 600
Set adoPRM(1) = adoCMD.CreateParameter("Publication", adChar,
adParamInput, 6, UserForm1.strPub)

adoCMD.Parameters.Append adoPRM(1)
Set adoRS = adoCMD.Execute

Unload UserForm1
Set UserForm1 = Nothing

Worksheets("Sheet1").Range("A5").CopyFromRecordset adoRS

Sheet1.Activate

End Sub

Sub ClearWorksheet()
Sheet1.Activate
Sheet1.Range("a5:az65000").Select
Selection.ClearContents
End Sub

Excel and Visual Basic
 

Does this work:

Sub ClearWorksheet()
Sheet1.Range("a:az").ClearContents
End Sub


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Pam" wrote in message
...
I have an excel 2007 spreadsheet that uses a Visual basic and sql to
populate. The procedure runs fine on all machines except two
machines. The program passes through one parameter and then clears
the work sheet and populates. The problem seems to be when the
worksheet is cleared. It just gets stuck

Sub ClearWorksheet()
Sheet1.Activate
Sheet1.Range("a5:az65000").Select
Selection.ClearContents
End Sub

If we change the above to only do this

Sub ClearWorksheet()
Sheet1.Activate
Sheet1.Range("a5:az1000").Select
Selection.ClearContents
End Sub

it runs but we need it to clear all 65000 rows or whatever the amount
might be.

Any body have any thoughts?

Here is the rest of the code:

Private Sub refreshbutton_Click()

Dim adoConnection As ADODB.Connection
Dim adoCMD As ADODB.Command
Dim adoRS As ADODB.Recordset
Dim adoPRM(1) As ADODB.Parameter
Dim strRange As String
Dim intRows As Integer


UserForm1.Show vbModal

If Not (UserForm1.blnContinue) Then
Unload UserForm1
Exit Sub
End If




ClearWorksheet

Set adoConnection = New ADODB.Connection
adoConnection.CursorLocation = adUseClient
adoConnection.ConnectionString =
"UID=;PWD=;DSN=SQL_server;DATABASE=MERION"
adoConnection.Open

Set adoCMD = New ADODB.Command
adoCMD.ActiveConnection = adoConnection
adoCMD.CommandType = adCmdStoredProc
adoCMD.CommandText = "XLS_rpt_SalesAnalysis"
adoCMD.CommandTimeout = 600
Set adoPRM(1) = adoCMD.CreateParameter("Publication", adChar,
adParamInput, 6, UserForm1.strPub)

adoCMD.Parameters.Append adoPRM(1)
Set adoRS = adoCMD.Execute

Unload UserForm1
Set UserForm1 = Nothing

Worksheets("Sheet1").Range("A5").CopyFromRecordset adoRS

Sheet1.Activate

End Sub

Sub ClearWorksheet()
Sheet1.Activate
Sheet1.Range("a5:az65000").Select
Selection.ClearContents
End Sub

Excel and Visual Basic
 

This problem can be caused by the Google Office COM-addins.

To switch them off:
Office Button--Excel Options--Addins--choose Com addins in the pulldown
at the bottom --Go--Uncheck all Google Desktop Addins

Charles
__________________________________________________
Outlines for my Sessions at the Australia Excel Users Group
http://www.decisionmodels.com/OZEUC.htm

"Pam" wrote in message
...
I have an excel 2007 spreadsheet that uses a Visual basic and sql to
populate. The procedure runs fine on all machines except two
machines. The program passes through one parameter and then clears
the work sheet and populates. The problem seems to be when the
worksheet is cleared. It just gets stuck

Sub ClearWorksheet()
Sheet1.Activate
Sheet1.Range("a5:az65000").Select
Selection.ClearContents
End Sub

If we change the above to only do this

Sub ClearWorksheet()
Sheet1.Activate
Sheet1.Range("a5:az1000").Select
Selection.ClearContents
End Sub

it runs but we need it to clear all 65000 rows or whatever the amount
might be.

Any body have any thoughts?

Here is the rest of the code:

Private Sub refreshbutton_Click()

Dim adoConnection As ADODB.Connection
Dim adoCMD As ADODB.Command
Dim adoRS As ADODB.Recordset
Dim adoPRM(1) As ADODB.Parameter
Dim strRange As String
Dim intRows As Integer


UserForm1.Show vbModal

If Not (UserForm1.blnContinue) Then
Unload UserForm1
Exit Sub
End If




ClearWorksheet

Set adoConnection = New ADODB.Connection
adoConnection.CursorLocation = adUseClient
adoConnection.ConnectionString =
"UID=;PWD=;DSN=SQL_server;DATABASE=MERION"
adoConnection.Open

Set adoCMD = New ADODB.Command
adoCMD.ActiveConnection = adoConnection
adoCMD.CommandType = adCmdStoredProc
adoCMD.CommandText = "XLS_rpt_SalesAnalysis"
adoCMD.CommandTimeout = 600
Set adoPRM(1) = adoCMD.CreateParameter("Publication", adChar,
adParamInput, 6, UserForm1.strPub)

adoCMD.Parameters.Append adoPRM(1)
Set adoRS = adoCMD.Execute

Unload UserForm1
Set UserForm1 = Nothing

Worksheets("Sheet1").Range("A5").CopyFromRecordset adoRS

Sheet1.Activate

End Sub

Sub ClearWorksheet()
Sheet1.Activate
Sheet1.Range("a5:az65000").Select
Selection.ClearContents
End Sub