Sub RunDataExtractionForEachDrawing()
Dim cadApp As Object
Dim folderPath As String
Dim templatePath As String
Dim dxeFileName As String
Dim excelFileName As String
Dim drawingName As String
Dim cadDoc As Object
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim quoteless
Dim excelFilePath
' Connect to AutoCAD application
On Error Resume Next
Set cadApp = GetObject(, "AutoCAD.Application")
If cadApp Is Nothing Then
Set cadApp = CreateObject("AutoCAD.Application")
End If
On Error GoTo 0
If cadApp Is Nothing Then
MsgBox "AutoCAD is not running or is not accessible."
Exit Sub
End If
' Specify the folder path containing the drawings
folderPath = "C:\\Users\\theodore.lee\\Desktop\\400774 Drawings\\Main\\400774\\"
' Specify the path to the template DXE file
templatePath = "C:\\Users\\theodore.lee\\Desktop\\sample.dxe" ' Update with your DXE template path
' Get a reference to the File System Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
' Get the folder object
Set objFolder = objFSO.GetFolder(folderPath)
' Loop through each file in the folder
For Each objFile In objFolder.Files
' Check if the file is a DWG file
If LCase(Right(objFile.Name, 4)) = ".dwg" Then
' Get the file name without extension
drawingName = objFSO.GetBaseName(objFile.Name)
' Specify the DXE and Excel file names
dxeFileName = folderPath & drawingName & "_DataExtract.dxe"
excelFilePath = "C:\\Users\\theodore.lee\\Desktop\\400774Excel\\"
excelFileName = excelFilePath & drawingName & "_DataExtract.xls"
' Copy the template DXE file to a new DXE file
objFSO.CopyFile templatePath, dxeFileName, True
' Open the drawing in AutoCAD
Set cadDoc = cadApp.Documents.Open(objFile.Path)
' Run the Data Extraction Wizard using the DXE file
cadApp.ActiveDocument.SendCommand Replace("_-DataExtraction " & Chr(34) & dxeFileName & Chr(34) & vbCrLf, """", "")
Name "C:\\Users\\theodore.lee\\Documents\\sample.xls" As excelFileName
' Save the extracted data as an Excel file
'cadApp.ActiveDocument.SendCommand Replace("_-ExportToExcel " & Chr(34) & excelFileName & Chr(34) & vbCrLf, """", "")
' Close the drawing without saving changes
cadDoc.Close False
End If
Next objFile
' Release AutoCAD objects
Set cadDoc = Nothing
Set cadApp = Nothing
Set objFSO = Nothing
Set objFolder = Nothing
Set objFile = Nothing
MsgBox "Data extraction completed for all drawings in the folder."
End Sub
Hi everyone,
I am trying to use VBA to automate the dataextraction process to get my BOM data from a specific block in my drawings to make an excel file for each drawing. This is my current process.
1. I have a .dxe file set up to extract the data that I need.
2. I'm using VBA to iterate through every .dwg file in a folder and opening them
3. It runs the _dataextraction and calls out the .dxe file
4. Output is saved to a unique excel sheet
Here's the problem that I'm running into, instead of each excel file being unique and containing the BOM information for their respective origin files, they all contain the same data which is the BOM information for the drawing that I set up the .dxe file to begin with. From my limited understanding of .dxe files, I believe that it contains the file path names that the data extraction pulls data from, so I tried to edit the .dxe files and replace all instances of the old file path with the desired file path but that didn't work. Do let me know if theres a way to manually edit the .dxe files to chance the source of data extraction.
Thank you
