open excel workbook - ole automation

[shastri_s]

How do I open an excel workbook using OLE automation.Although using Getobject and createobject, I am able to start instances of Excel apllcn., I have not been able to figure a way of opening an excel workbook.. say important.xls as parameter.

Any ideas

[venkatraman_r]

Hi Shastri,

Hope this works for you,

Code Begins here:
----------------

Dim FileName As String

On Error GoTo VenError

Dim oExcel As New Excel.Application
Dim oWorksheet As Excel.Worksheet

oExcel.Workbooks.Open "important.xls"


no = InputBox("How many rows you need to retrieve?", "Enter Rows Count")

For i = 3 To no
For j = 1 To 2
Print oExcel.Workbooks.Item(1).Worksheets.Item(1).Cells(i, j).Value & Chr(13)
Next j
Next i

Exit Sub

VenError:

strMsg = "The Specified File Could not be found/opened." & Chr(13)
strMsg = strMsg & "Please enter a valid file name"
MsgBox strMsg, vbOKOnly + vbQuestion, "Error"

--------------
Code Ends here:
--------------

if you dont want to specify the no. of rows do the needful validations.

Good Luck,

Venkat.

[venkatraman_r]

Hi Shastri,

Hope this works for you,

Code Begins here:
----------------

Dim FileName As String

On Error GoTo VenError

Dim oExcel As New Excel.Application
Dim oWorksheet As Excel.Worksheet

oExcel.Workbooks.Open "important.xls"


no = InputBox("How many rows you need to retrieve?", "Enter Rows Count")

For i = 3 To no
For j = 1 To 2
Print oExcel.Workbooks.Item(1).Worksheets.Item(1).Cells(i, j).Value & Chr(13)
Next j
Next i

Exit Sub

VenError:

strMsg = "The Specified File Could not be found/opened." & Chr(13)
strMsg = strMsg & "Please enter a valid file name"
MsgBox strMsg, vbOKOnly + vbQuestion, "Error"

--------------
Code Ends here:
--------------

if you dont want to specify the no. of rows do the needful validations.

Good Luck,

Venkat.

[Al Smith]

shastri_s,
Hi. Here's how I do this.
It also save's as a new filename.
Al.

Sub DataToExcel()
Set XLsheet = GetObject("c:\excel.xls\Inventory Value by Item by Warehouse")
XLsheet.Application.Visible = True
XLsheet.Parent.Windows(1).Visible = True
XLsheet.Worksheets("Whse 100").Activate
'Add data to worksheet e.g.
XLsheet.ActiveSheet.Cells(24, 7).Value = yourdata
XLsheet.Worksheets("Whse 200").Activate
'Add data to worksheet
XLsheet.Worksheets("Whse 300").Activate
'Add data to worksheet
DateStamp = Date
For x = 1 To Len(DateStamp)
If Mid(DateStamp, x, 1) = "/" Then Mid(DateStamp, x, 1) = "-"
Next x
XLsheet.SaveAs "c:\excel.xls\Inventory Value " & DateStamp
XLsheet.Application.Quit
Set XLsheet = Nothing
Exit Sub
End Sub

[shastri_s]

Thanks, Venkat.

For closing do I just say .workbooks.close