-
Gaffer , thanks for your suggestion , I think that will work and I have tried out some small trials and all seems to be working.
Private Sub Command1_Click()
Dim spreadsheetname As String
Dim objXLS As New Excel.Application
spreadsheetname = "c:\funnys\book2.xls"
With objXLS
.Workbooks.Open spreadsheetname
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("f1").Select
ActiveCell.FormulaR1C1 = "xxx"
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("f1").Select
ActiveCell.FormulaR1C1 = "xxx"
ActiveWorkbook.Save
End With
Set objXLS = Nothing
MsgBox "finito"
End Sub
what I need to know is if the excel workbook has several sheets inside it , how do I name the sheet I want to use. In the example I have above I just name the workbook to open ie book2 but how do I tell it to open book2 and work with sheet 3 inside the work book.
thx for any help
Locutus
-
Hi locutus !!!
First of all don't use selection, it's pretty slow
Use instead references "SET xxx = "
Second u can name or give the number of the sheet
see code below.
if u have any question - feel free do ask
cu TheOnly
PS: VB not available in the moment so the code is not tested, but should work
Code:
Private Sub Command1_Click()
'define name as const it never changes
const spreadsheetname As String = "c:\funnys\book2.xls"
'make sure u have a refernce to excel
Dim objXLS As New Excel.Application
'dim a reference to a workbook
dim wbkBook as excel.workbook
'dim a reference to a sheet
dim shtSheet as excel.Worksheet
'set the excel object
set objxls = new Excel.application
set a reference to a workbook - in this case we open one
set wbkbook = objxls.workbooks.open spreadsheetname
'Numbered referenced - first sheet in collection
set shtsheet = wbkbook.worksheets(1)
'or named referenced
set shtsheet = wbkbook.worksheets("MySheet")
with shtsheet
.Rows("1:1").insert Shift:=xlDown
.range("f1").FormulaR1C1 = "xxx"
end with
set shtsheet = nothing
wbkbook.close savechanges:=true
set wbkbook = nothing
objxls.quit
Set objXLS = Nothing
MsgBox "finito"
End Sub
