Need help fast!!! Worksheet keeps closing!
Posted by CJ on February 06, 2001 8:37 AM
I have a button on an excel worksheet with VBA code behind it... The code creates a new worksheet and populates it with an Access query. My code is below... The problem is that the code runs and then by the time it gets to the end of the code, the new worksheet closes! Anyone know how to get the worksheet to stay after the code runs???
Private Sub cmdRefreshData_Click()
Dim rec As Recordset
Dim rge As Range
Dim intRows As Integer
Dim intFields As Integer
Dim strSelect As String
Dim strConn As String
Dim db As Database
Dim wsp As Workspace
Set ExportSheet = CreateObject("Excel.Sheet")
ExportSheet.Application.Visible = True
ExportSheet.Application.WindowState = xlMaximized
ActiveSheet.name = "CNA"
Set wsp = DBEngine.Workspaces(0)
Set db = wsp.OpenDatabase("n:\CNA.mdb")
db.QueryTimeout = 5000
Set rge = ActiveSheet.Range("a7")
Set rec = db.OpenRecordset("Query8")
rec.MoveLast
intRows = rec.RecordCount
rec.MoveFirst
intFields = rec.Fields.Count
'pastes field names
For intCount1 = 0 To intFields - 1 'do as many times as there are fields
rge.Cells(1, intCount1 + 1).Value = rec.Fields(intCount1).name
Next intCount1
'pastes field values
For intcount2 = 0 To intRows - 1 'do this as many times as there are rows
For intcount3 = 0 To intFields - 1 'do this as many times as there are fields
rge.Cells(intcount2 + 2, intcount3 + 1).Value = rec.Fields(intcount3).Value
Next intcount3
rec.MoveNext
Next intcount2
rec.Close
ActiveSheet.Columns.AutoFit
db.Close
End Sub
Posted by Dave Hawley on February 06, 2001 8:58 AM
Set ExportSheet = CreateObject("Excel.Sheet") ExportSheet.Application.Visible = True ExportSheet.Application.WindowState = xlMaximized ActiveSheet.name = "CNA" Set wsp = DBEngine.Workspaces(0) Set db = wsp.OpenDatabase("n:\CNA.mdb") db.QueryTimeout = 5000 Set rge = ActiveSheet.Range("a7") Set rec = db.OpenRecordset("Query8") rec.MoveLast intRows = rec.RecordCount rec.MoveFirst intFields = rec.Fields.Count 'pastes field names For intCount1 = 0 To intFields - 1 'do as many times as there are fields rge.Cells(1, intCount1 + 1).Value = rec.Fields(intCount1).name Next intCount1 'pastes field values For intcount2 = 0 To intRows - 1 'do this as many times as there are rows For intcount3 = 0 To intFields - 1 'do this as many times as there are fields rge.Cells(intcount2 + 2, intcount3 + 1).Value = rec.Fields(intcount3).Value Next intcount3 rec.MoveNext Next intcount2 rec.Close ActiveSheet.Columns.AutoFit db.Close End Sub
Hi Cj
As you are doing this from within excel, could you replace your sheet code with:
Application.DisplayAlerts = False
Set ExportSheet = Sheets.Add().Name = "CNA"
If ActiveSheet.Name <> "CNA" Then ActiveSheet.Delete
Application.DisplayAlerts = True
Dave
OzGrid Business Applications
Posted by CJ on February 06, 2001 9:11 AM
As you are doing this from within excel, could you replace your sheet code with: Application.DisplayAlerts = False
THanks - I tried that, but I get a type mismatch error on this line :
Set ExportSheet = Sheets.Add().Name = "CNA"
You meant to insert your code instead of this right?
'Set ExportSheet = CreateObject("Excel.Sheet")
'ExportSheet.Application.Visible = True
'ExportSheet.Application.WindowState = xlMaximized
'ActiveSheet.name = "CNA"
For some reason I can't get rid of this error... ANy suggestions??? Thanks!
Posted by Dave Hawley on February 06, 2001 9:45 AM
That will teach mme for not testing!
Try this method instead. And yes I did mean it to replace the lines of code you posted.
Application.DisplayAlerts = False
Sheets.Add().Name = "CNA"
If ActiveSheet.Name <> "CNA" Then ActiveSheet.Delete
Set ExportSheet = Sheets("CNA")
Application.DisplayAlerts = True
OzGrid Business Applications
Posted by CJ on February 06, 2001 10:07 AM
Thanks so much! That did it! :)
