memory can't read

[parkfairfax]

I use the following code fine to open numerous spreadsheets and it opens them fine...


Dim wks As Workbook, strwks As String
strwks = "c:\Reports\Test4.xls"
Set wks = GetObject(strwks)
wks.Parent.Visible = True
wks.Windows(1).Visible = True
wks.Worksheets("FY01&quot.Select
Set wks = Nothing

However, now new spreadsheeets that I create will open, but as soon as I click anywhere in the open spreadsheet, ie. the open window or a different worksheet, I get the following message..

The Instruction as "0x304571d" referenced memory at "0x000000f4". The memory could not be read. Click OK to terminate program, Click cancel to debug.

I thought that this was happening because I was renaming my worksheets & VBA was not getting it, but now I see that even new workbooks that I create do not open correctly.

Does anybody have any ideas what's going on?

Thanks,

garry

 

[Databaseguy]

You set the object to nothing, which is good coding practice.However,in this instance, you're destroying the very object your trying to use.

Remove the Set wks = Nothing statement and you should be good.
Tyrone Lumley
augerinn@gte.net

http://server105.hypermart.net/databaseguy

 

[parkfairfax]

thanks, I tried that, but it still doesn't work. This is mystifying.

 

[Databaseguy]

Hmmm.....

How are you instanciating the Excel Object ??? Here's a few ways I've used to open Excel from Access:

Private Sub Command0_Click()
Static objExcel As Excel.Application
Set objExcel = New Excel.Application
With objExcel
.Visible = True
.Workbooks.Add
.Cells(1, 1).Formula = "Ty"
End With
Set objExcel = Nothing
End Sub

Private Sub Command1_Click()
Dim objExcel As Excel.Application
Set objExcel = New Excel.Application
With objExcel
.Visible = True
.Workbooks.Add
.Cells(1, 1).Formula = "Ty"
End With

End Sub

Private Sub Command2_Click()
Dim objExcel As Excel.Application
Set objExcel = CreateObject("Excel.Application&quot
With objExcel
.Visible = True
.Workbooks.Add
.Cells(1, 1).Formula = "Ty"
End With

End Sub
Tyrone Lumley
augerinn@gte.net

http://server105.hypermart.net/databaseguy

 

[parkfairfax]

I don't understand your code. What part of it do you use to define and open your spreadsheet?

Thank you.

 

[JoaoTL]

Hi, with this function i think you will not have any problem. Hope it helps

Function OpenBook(strFilePath As String) As Boolean
' This procedure checks to see if the workbook
' specified in the strFilePath argument is open.
' If it is open, the workbook is activated. If it is
' not open, the procedure opens it.
Dim wkbCurrent As Excel.Workbook
Dim strBookName As String

On Error GoTo OpenBook_Err

' Determine the name portion of the strFilePath argument.
strBookName = NameFromPath(strFilePath)
If Len(strBookName) = 0 Then Exit Function
If Workbooks.Count > 0 Then
For Each wkbCurrent In Workbooks
If UCase$(wkbCurrent.Name) = UCase$(strBookName) Then
wkbCurrent.Activate
Exit Function
End If
Next wkbCurrent
End If
Workbooks.Open strFilePath
OpenBook = True

OpenBook_End:
Exit Function
OpenBook_Err:
OpenBook = False
Resume OpenBook_End
End Function Best Regards

---
JoaoTL
NOSPAM_mail@jtl.co.pt

http://www.jtl.co.pt

http://www.jtl.co.pt/Access