VBA Newbie Help...

[kamden]

Hello everyone.

I am a VBA Rookie and I am trying to do, what started and seemed as a simple thing, in Autocad.

I am trying to make a simple form with 2 buttons and 2 listboxes with the following functions:

Button 1 : Finds all unique block names in the Drawing and places them in ListBox 1
Finds the total number of every unique block name in the drawing and adds it to ListBox2.


Button 2: Transfers the values of listbox2 in certain excel cells in a readymade excel workbook, depending on the block names in listbox1.

Button 3 : Exits the form/application

The worst problem I am encountering is counting the number of Blocks in the Drawing. I mean I am reading hundreds of forums and even found ready made vba code but nothing seems to work as I expect. So I try to make everything from start but (due to my lack of knowledge on arrays or multidimensional arrays) I am stuck on the point that I want to count the number of every unique block name in the drawing and add it to my listbox.

Here is the code that I have so far:

Public excelApp As Object
Public wkbkObj As Object
Public sheetObj As Object

Private Sub CommandButton1_Click()
Dim i, j, BlocksTotal As Integer
Dim Block As AcadBlockReference
Dim BlockName, UniqueBlockName As String
Dim Blk As AcadEntity

'Number of unique blocks
btot = ThisDrawing.Blocks.Count

' Every unique block name goes in Listbox1
For i = 0 To btot - 1
    UniqueBlockName = ThisDrawing.Blocks.Item(i).Name
    If Not Mid$(UniqueBlockName, 1, 1) = "*" And Not UniqueBlockName = "NAME" Then ListBox1.AddItem UniqueBlockName ' Getting rid of *ModelSpace and *PaperSpace and a wrong name of block I have in all drawings
Next i

For j = 0 To ListBox1.ListCount - 1 ' The problem is in this Loop...I cannot find a fast and easy way to count the number of every block name
     UniqueBlockName = ListBox1.List(j) 'picking up block names from listbox1
     BlocksTotal = 0
        For Each Blk In ThisDrawing.ModelSpace ' Here I want to check the name of every block in modelspace (not unique) and if it is the same as the listbox name i have to find its total number and add it to listbox 2
            If Blk.Name = UniqueBlockName Then BlocksTotal = BlocksTotal + 1
        Next
     ListBox2.AddItem BlocksTotal
Next j
End Sub

Private Sub CommandButton2_Click()
Dim i As Integer
On Error Resume Next
    Set excelApp = GetObject(, "Excel.Application")
    If Err <> 0 Then
        Err.Clear
        Set excelApp = CreateObject("Excel.Application")
                If Err <> 0 Then
            MsgBox "ÓöÜëìá êáôÜ ôçí åêêßíçóç ôïõ Excel!", vbExclamation
            End
        End If
    End If
    excelApp.Visible = True
    Set wkbkObj = excelApp.Workbooks.Open(FileName:="c:\dipola.xls")
    Set sheetObj = wkbkObj.Worksheet(2)
For i = 0 To ListBox1.ListCount - 1
    If ListBox1.List(i) = "C13" Then sheetObj.Range("A1").Value = ListBox2.List(i)
    If ListBox1.List(i) = "C1" Then sheetObj.Range("C9").Value = ListBox2.List(i)
Next i
End Sub

Private Sub CommandButton3_Click()
End
End Sub

Can anyone help me because I've lost 2 days trying to find something that really works...I wish I had time to read more and try other methods but I need it ASAP and I am utterly confused

Thanks in advance...

FYI : I am using Autocad 2011 LT.

[MSasu]

Not sure about the version you stated (both in Profile and in your post) - there is no customization solution available in LT?!?

Don't want to discourage you, but hope you are aware that VBA is discontinued from AutoCAD since 4 releases. Is to be supposed that one day will not be supported anymore.
Since you are just at beginning is much better to look for .Net approach instead.

[kamden]

The thing is that we need this one time (in order to produce reports from 485 drawings).
Then it will not be used anymore.

I installed VBA separately in autocad (I saw that it was not supported anymore).
To tell you the truth everything seems to work fine except the problem with names that i am stating.
In order to finish this one time work I can try in other autocad versions as well ( I have 2009 and 2007 installations in other PC's too).

I just need to find out how to do it properly.

[MSasu]

What are you trying to achieve with that code? Count the instances of blocks in drawings and export them in Excel? There is the built-in BCOUNT command available - although I'm not sure if is available in LT.

[MSasu]

I installed VBA separately in autocad (I saw that it was not supported anymore).

Again, what are you saying is that had the VBA extension (enabler) installed on AutoCAD 2011 LT?

[kamden]

What are you trying to achieve with that code? Count the instances of blocks in drawings and export them in Excel? There is the built-in BCOUNT command available - although I'm not sure if is available in LT.

I want to export them in certain cells in an excel report.
LT doesn't have express tools and also it would be time consuming to copy paste thousands of values for all these drawings. That is why we try to do it with code.

[Tyke]

Try getting the names of all of the blocks in the drawing from the blocks collection and filter each block name to get the number of instances of the block references. Then you can inser them in your list boxes.

The point that MSasu is correctly making is that you will NOT be able to run this VBA code in LT. And there's no way round that.

[BIGAL]

Does LT have Dataextract that will probably do what you want in 1 go it made to output to excel.

[kamden]

I worked out a solution that works perfectly by using a multidimensional array.

The code is:

Dim excelApp As Excel.Application
Dim wbk As Workbook
Dim sht As Worksheet

Private Sub cmdListBlocks_Click()
    
    Dim Block As AcadBlock
    Dim i As Integer
    Dim MyBlockArray() As Variant
    
    i = 0
    
    For Each Block In ThisDrawing.Blocks
    i = i + 1
    
    ReDim Preserve MyBlockArray(2, i)
        MyBlockArray(0, i) = Block.Name
        MyBlockArray(1, i) = Block.Count
    Next Block
    Me.ListBoxBlocks.ColumnCount = 2
    Me.ListBoxBlocks.ColumnWidths = "36;36"
    Me.ListBoxBlocks.Column() = MyBlockArray
End Sub

Private Sub CommandButton1_Click()

Dim i As Integer

ListCount = Me.ListBoxBlocks.ListCount

On Error Resume Next
    Set excelApp = GetObject(, "Excel.Application")
    If Err <> 0 Then
        Err.Clear
        Set excelApp = CreateObject("Excel.Application")
                If Err <> 0 Then
            MsgBox "Εrror Opening Excel!", vbExclamation
            End
        End If
    End If
    excelApp.Visible = True
    Set wkbkObj = excelApp.Workbooks.Open(FileName:="c:\dipola.xls") ' here i just open a certain excel Workbook
    Set sheetObj = wkbkObj.Worksheet(1) ' I put the values in the 2nd sheet

For i = 0 To ListCount - 1
    If ListBoxBlocks.List(i, 0) = "C1" Then Range("B1").Cells.Value = ListBoxBlocks.List(i, 1)
    If ListBoxBlocks.List(i, 0) = "C2" Then Range("B2").Cells.Value = ListBoxBlocks.List(i, 1)  ' Here I make a lot of checks so i just wrote two to show the way I send values to certain cells in excel
Next

wkbkObj.Sheets("DIPOLA").Select  ' I focus on the 1st sheet that is the actual report page for the user to print

End Sub

Private Sub CommandButton2_Click()
End
End Sub

I just created a UserForm with 3 buttons and a listbox (just to read the values from and send them to excel).

I'm not sure that it will work in 2011 LT but in 2009 and 2007 it does.

[Tyke]

I'm not sure that it will work in 2011 LT but in 2009 and 2007 it does.

It will definitely NOT work in AutoCAD 2011 LT only in a full version of AutoCAD.