I am having trouble opening (for mo

[MackOfTrades]

I am having trouble opening (for modification) an excel file using Visual Basic 6.0
I have no problem creating a new file in order to save data, but when I wish to open this file, I am unable to do so. When you are dimensioning everything, do you dimension the application as new, or the workbook, or the worksheet? There is only 1 worksheet in the workbook. What is the correct code to open this? Thanks for any help.

 

[johnwm]

Try this:

Dim wb As Excel.Workbook
Dim sht1 As Excel.Worksheet
Dim myRange As Excel.Range
Dim appw As Excel.Application
On Error Resume Next 'ignore errors
Set appw = GetObject(, "Excel.Application&quot
If Err.Number <> 0 Then 'If Excel is not running then
Set appw = CreateObject(&quot;Excel.Application&quot 'run it
End If
Err.Clear ' Clear Err object in case error occurred.
On Error GoTo 0 'Resume normal error processing
Set wb = appw.Workbooks.Open(&quot;c:\calcs.xls&quot
Set sht1 = wb.Sheets(1)

Make sure you close fully when done:

For Each wb In Workbooks
wb.Close savechanges:=True
Next wb
Set appw = Nothing
Set wb = Nothing

Let me know if this helps
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'There are 10 kinds of people in the world: those who understand binary, and those who don't.'

 

[MackOfTrades]

Ok, you can all laugh at my attempts to program in VB. After looking at your code (which helped me out with other problems I've been having), here is what I have.

Private Sub cmdstart_Click()

Dim FileHandle%
Dim strfilename As String
Dim intA As Integer
Dim strtemp As String 'Temp string to hold data

Dim excelapplication As Excel.Application
Dim excelworkbook As Excel.Workbook
Dim excelworksheet As Excel.Worksheet
Dim excelrange As Excel.Range

If cdViewer.FileName <> &quot;&quot; Then
strfilename = cdViewer.FileName
FileHandle% = FreeFile
Open strfilename For Input As #FileHandle%
MousePointer = vbHourglass

Set excelworkbook = excelapplication.excelworkbook.Open(strfilename)
Set excelworksheet = excelworkbook.Worksheet

............other code not important removed..........

MousePointer = vbDefault

excelworkbook.Close

Close #FileHandle%
End If

End Sub

The error that I get when I run this is....
&quot;Run-time error '91':
Object variable or With block variable not set.&quot;

I know that my code has instances in which it could fail due to invalid input, but right now I'm just trying to sketch a vague outline. I'll fill it all in later.

Thanks for any suggestions.

 

[johnwm]

I think you need to 'Set' the excelapplication before using it.

As in (from previous post)
On Error Resume Next 'ignore errors
Set appw = GetObject(, &quot;Excel.Application&quot
If Err.Number <> 0 Then 'If Excel is not running then
Set appw = CreateObject(&quot;Excel.Application&quot 'run it
End If
Err.Clear ' Clear Err object in case error occurred.
On Error GoTo 0 'Resume normal error processing

Obviously change appw to excelapplication Let me know if this helps
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'There are 10 kinds of people in the world: those who understand binary, and those who don't.'

 

[MackOfTrades]

Much thanks. You're answers finally got me past this point of aggravation. For whatever reason, it took me quite a while of fiddling, but here is what I finally ended up with... and what works.

Dim strfilename As String
Dim intA As Integer
Dim strtemp As String 'Temp string to hold data
Dim endnumber As Integer

Dim excelapplication As Excel.Application
Dim excelworkbook As Excel.Workbook
Dim excelworksheet As Excel.Worksheet
Dim myrange As Excel.Range

Set excelapplication = New Excel.Application

If cdViewer.FileName <> &quot;&quot; Then
strfilename = cdViewer.FileName
MousePointer = vbHourglass

Set excelworkbook = excelapplication.Workbooks.Open(strfilename)
Set excelworksheet = excelworkbook.Sheets(1)

'''''''Code to be done'''''''

MousePointer = vbDefault

excelworkbook.Close

End If


As I said, I can't thank you enough. I really feel like I've gotten past the main hump now that you've helped me figure this out. Thanks again.