excel interop - how to avoid late binding?

  • Thread starter Thread starter GS
  • Start date Start date
G

GS

I have installed the ms PIA for ofc XP, and followed the article
http://support.microsoft.com/kb/247412/
trying to paste into a worksheet

However I got late binding not allowed errors


....
webOCWraooer,Copy // get the desired data into clapboard

'Create a new workbook in Excel
Dim oExcel As Object
Dim oBook As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add ' < option strict on disallows late
binding>


'Paste the data
oBook.Worksheets(1).Range("A1").Select ' < option strict on disallows
late binding>

oBook.Worksheets(1).Paste ' < option strict on disallows late binding>



so I tried

....
Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
oExcel = New Excel.Application
oBook = oExcel.Workbooks.Add ' so far so good
'Paste the data
oBook.Worksheets(1).Range("A1").Select() ' oops late binding
error
oBook.Worksheets(1).Paste() ' same as above

How do get around the last two?

I tried
oSheet As Excel.Worksheets
Dim oRange As Excel.Range

oSheet = oBook.ActiveSheets(1)
but that did not help


please Help
 
Now I have cut down the errors to one:
Dim oSheet As Excel.Worksheet
Dim oRange As Excel.Range

oSheet = oExcel.ActiveWorkbook.ActiveSheet() ' < still
implicit conv form obj to microsoft.office.interop.excel.worksheet
' also tried oExcel.ActiveSheet
' do not work: oBook.Worksheets(1) 'oBook.Sheets
 
I think I got the answer by a twisted way.
I was stuck in vb, I went to C# got something the syntactically correct, get
the code fragment translated back to vb using online converter and here is
something VB will accept:

import Microsoft.Office.Interop

'Create a new workbook in Excel

'Create a new workbook in Excel
Dim oExcel As Microsoft.Office.Interop.Excel.Application
Dim oBook As Microsoft.Office.Interop.Excel.Workbook
oExcel = New Microsoft.Office.Interop.Excel.Application()
oBook = New Microsoft.Office.Interop.Excel.Workbook()

'Paste the data

Dim oSheet As Microsoft.Office.Interop.Excel.Worksheet
Dim oRange As Microsoft.Office.Interop.Excel.Range
oSheet = DirectCast(oBook.ActiveSheet,
Microsoft.Office.Interop.Excel.Worksheet)
' aha, the trick is to use directcast

oRange = oSheet.Range("A1")
oSheet.Paste()
 
Back
Top