How to get data from the correct Excel sheet

[Jerune]

Hello,

I made a VBA code to import Text, X and Y-cordinates from and excel sheet to Acad. The problem i'm facing now is that for some reason it doesnt take the data from the correct Excel worksheet.

This is the code i'm using code:

 

Sub Main()
 Dim ExcAP As Excel.Application
 Dim ExcWB As Excel.Workbook
 Dim ExcWS As Excel.Worksheet
 Set ExcAP = Excel.Application
 Set ExcWB = ExcAP.Workbooks.Open("Z:\WRK_Jeroen\testing excel autocad blad2.xls")
 Set ExcWS = ExcWB.Worksheets(1)
 
   
Dim I As Integer
Dim Height As Double
Dim P(0 To 2) As Double
Dim TxtObj As AcadText
Dim TxtStr As String
Height = 1

With Worksheet

For I = 0 To 10
 
 P(0) = Cells(1 + I, 2): P(1) = Cells(1 + I, 3): P(2) = 0
 TxtStr = Cells(1 + I, 1)
 Set TxtObj = ThisDrawing.ModelSpace.AddText(TxtStr, P, Height)
Next I
 
End With
  
 ExcAP.Workbooks.Close
 ExcAP.Quit
 Set ExcAP = Nothing
End Sub

 

I also tryed to use the name of the worksheet instead of the numer but also that didnt work.

I hope someone knows what the problem is.

 

Cheers Jeroen

[Commandobill]

Although I have never done this in VBA only VLA I believe the hierarchy is...

Application -> Workbooks -> Open -> Sheets -> Item 1 (this is where you put in which sheet you want) -> Cells

 

in VLA it looks like this

 

(setq wCells (vlax-get (vlax-get-property (vlax-get (vla-open (vlax-get (vlax-get-or-create-object "Excel.Application") "Workbooks") thefilename) "Sheets") "Item" 1) "Cells"))

 

Hope this helps

[spiff88]

I think your problem is here...the With Worksheet is never used, because you have no "." before the Cells statements.

 

Try this:

 

 

With Worksheet

For I = 0 To 10
 
 P(0) = [color="Red"].[/color]Cells(1 + I, 2): P(1) = [color="Red"].[/color]Cells(1 + I, 3): P(2) = 0
 TxtStr = [color="Red"].[/color]Cells(1 + I, 1)
 Set TxtObj = ThisDrawing.ModelSpace.AddText(TxtStr, P, Height)
Next I
 
End With

 

Or don't use the With Worksheet - just refer to the cells directly...

 

P(0) = [color="Red"]ExcWS.[/color]Cells(1 + I, 2)

[Jerune]

Thanks a lot guys! You really helped me out!