Batch Plot to Pdf using Excel Points

[Anonymous]

I have developed this code but i am not getting values in point1(0 to 1) , point2(0 to 1).Here is the full code.It shows empty when i run.

Option Explicit
Public excelApp As Object
Public wbkObj As Object
Public shtObj As Object
Public point1(0 To 1) As Double, point2(0 To 1) As Double


Sub Example_SetWindowToPlot()

' This example plots a pdf file for each frame as the for function selects each frame.
On Error Resume Next
Dim i As Integer
Dim mRow As Integer
Dim backP As Integer

Set excelApp = GetObject(, "Excel.Application")
If Err <> 0 Then
Err.Clear
Set excelApp = CreateObject("Excel.Application")
If Err <> 0 Then
MsgBox "Could not start Excel", vbExclamation
End
End If
End If
excelApp.Visible = True
Set wbkObj = excelApp.Workbooks.Open(filename:="e:\2.xlsx")
Set shtObj = excelApp.Worksheets(1)

mRow = shtObj.Cells.SpecialCells(xlCellTypeLastCell).Row

'Desactivate background plot

For i = 1 To mRow
' Get first point in window

Set excelApp.shtObj.Range(i, 1).Value = point1(0): Set excelApp.shtObj.Range(i, 2).Value = point1(1)
' Get second point in window
Set excelApp.shtObj.Range(i, 3).Value = point2(0): Set excelApp.shtObj.Range(i, 4).Value = point2(1)

AppActivate ThisDrawing.Application.Caption
backP = ThisDrawing.GetVariable("BACKGROUNDPLOT")
ThisDrawing.SetVariable "BACKGROUNDPLOT", 0
'Select printing config
ThisDrawing.ActiveLayout.ConfigName = "DWG to PDF.pc3"

' Send information about window to current layout
ThisDrawing.ActiveLayout.SetWindowToPlot point1, point2

' Read back window information
ThisDrawing.ActiveLayout.GetWindowToPlot point1, point2

' Configure Centerplot to be true
ThisDrawing.ActiveLayout.CenterPlot = True

' Be sure to plot a view, not some other plot style
ThisDrawing.ActiveLayout.PlotType = acWindow

'Name pdf file
Dim plotFileName As String
plotFileName = "C:\Temp\My PDF Plot" & i

'Print pdf
Dim result As Boolean

result = ThisDrawing.Plot.PlotToFile(plotFileName)

Next i

ThisDrawing.SetVariable "BACKGROUNDPLOT", backP


End Sub

[norman.yuan · Accepted solution]

You have these 2 lines of code going backward:

' Get first point in window
Set excelApp.shtObj.Range(i, 1).Value = point1(0): Set excelApp.shtObj.Range(i, 2).Value = point1(1)
' Get second point in window
Set excelApp.shtObj.Range(i, 3).Value = point2(0): Set excelApp.shtObj.Range(i, 4).Value = point2(1)

They should be:

' Get first point in window
point1(0) = excelApp.shtObj.Range(i, 1).Value: point1(1) = excelApp.shtObj.Range(i, 2).Value
' Get second point in window
point2(0) = excelApp.shtObj.Range(i, 3).Value: point2(1) = excelApp.shtObj.Range(i, 4).Value