for Gaffer or anyone who knows about creating excel objects in VB

[locutus]

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

[TheOnly]

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

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