Printable View
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
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.
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.
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
Thanks, Venkat.
For closing do I just say .workbooks.close