Hi all,
The following macro works only in active sheets.
Can you please advise how can we do, that this works in all worksheets?
Or all just the worksheets which i needs that works.
The following macro works only in active sheets.
Can you please advise how can we do, that this works in all worksheets?
Or all just the worksheets which i needs that works.
Code:
Option Explicit
'A custom type that holds the scale factors of the block.
Private Type ScaleFactor
X As Double
Y As Double
Z As Double
End Type
Sub InsertBlocks()
'--------------------------------------------------------------------------------------------------------------------------
'Declaring the necessary variables.
Dim acadApp As Object
Dim acadDoc As Object
Dim acadBlock As Object
Dim LastRow As Long
Dim I As Long
Dim InsertionPoint(0 To 2) As Double
Dim BlockName As String
Dim BlockScale As ScaleFactor
Dim RotationAngle As Double
[COLOR=#ff0000] With Sheets(ActiveSheet.Name)[/COLOR]
.Activate
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
'Check if there are coordinates for at least one circle.
If LastRow < 2 Then
MsgBox "There are no coordinates for the insertion point!", vbCritical, "Insertion Point Error"
Exit Sub
End If
'Check if AutoCAD application is open. If is not opened create a new instance and make it visible.
On Error Resume Next
Set acadApp = GetObject(, "AutoCAD.Application")
If acadApp Is Nothing Then
Set acadApp = CreateObject("AutoCAD.Application")
acadApp.Visible = True
End If
'Check (again) if there is an AutoCAD object.
If acadApp Is Nothing Then
MsgBox "Sorry, it was impossible to start AutoCAD!", vbCritical, "AutoCAD Error"
Exit Sub
End If
On Error GoTo 0
'If there is no active drawing create a new one.
On Error Resume Next
Set acadDoc = acadApp.ActiveDocument
If acadDoc Is Nothing Then
Set acadDoc = acadApp.Documents.Add
End If
On Error GoTo 0
'Check if the active space is paper space and change it to model space.
If acadDoc.ActiveSpace = 0 Then '0 = acPaperSpace in early binding
acadDoc.ActiveSpace = 1 '1 = acModelSpace in early binding
End If
On Error Resume Next
'Loop through all the rows and add the corresponding blocks in AutoCAD.
[COLOR=#ff0000] With Sheets(ActiveSheet.Name)[/COLOR]
For I = 2 To LastRow
'Set the block name.
BlockName = .Range("D" & I).value
'If the block name is not empty, insert the block.
If BlockName <> vbNullString Then
'Set the insertion point.
InsertionPoint(0) = .Range("A" & I).value
InsertionPoint(1) = .Range("B" & I).value
InsertionPoint(2) = .Range("C" & I).value
'Initialize the optional parameters.
BlockScale.X = 1
BlockScale.Y = 1
BlockScale.Z = 1
RotationAngle = 0
'Add the block using the sheet data (insertion point, block name, scale factors and rotation angle).
'The 0.0174532925 is to convert degrees into radians.
Set acadBlock = acadDoc.ModelSpace.InsertBlock(InsertionPoint, BlockName, _
BlockScale.X, BlockScale.Y, BlockScale.Z, RotationAngle * 0.0174532925)
' Get the attributes for the block reference
Dim varAttributes As Variant
varAttributes = acadBlock.GetAttributes
' Move the attribute tags and values into a string to be displayed in a Msgbox
Dim strAttributes As String
Dim K As Integer
For K = LBound(varAttributes) To UBound(varAttributes)
strAttributes = strAttributes & vbLf & " Tag: " & varAttributes(K).TagString & _
vbLf & " Value: " & varAttributes(K).TextString & vbLf & " "
Next
End If
Next I
End With
'Zoom in to the drawing area.
acadApp.ZoomExtents
End Sub
