Free Excel File Samples Downloads with useful and most popular task which could be solved by internal Visual Basic For Applications (VBA) macros and scripts.
For NanoCAD Free/AutoCAD automation and Excel files with Macro running please adjust settings for Microsoft Visual Basic for Application please do these steps:
NanoCAD Free users:
1. Open Excel file, press “ALT+F11” to open Microsoft Visual Basic for Application, then “Tools - References”, add: “nanoCAD Type Library (NCAuto.dll)” “OdaX Type Library (OdaX_csd.dll)”, then press OK.
AutoCAD users:
1. Open Excel file, press “ALT+F11” to open Microsoft Visual Basic for Application, then “Tools - References”, add: “AutoCAD YYYY Type Library” file” (acaxXXenu.tlb), where YYYY – YEAR of your AutoCAD Version, XX – 21, 20, 19,…(version number); “AutoCAD Focus Control for VBA Type Library” (AcFocusCtrlXX.dll); “AXDBLib” (axdbXXenu.tlb), then press OK.
If your DWG files use blocks for the similar information such as Dates, titles, etc. and these text are placed in multiple places in your drawings, you can automate replacing their text content from the template DWG file to edition DWG file state. If your project DWG files contents 50-100, etc. blocks, manual text replace will take some time. Simply create one template DWG file once and next time change data you need to replace fast in our Excel file with Macro.
For quick text replacement in multiple blocks of DWG files use free version of NanoCAD or AutoCAD commercial software.
6. Close Microsoft Visual Basic for Application editor.
7. Enable Macros in Excel https://support.office.com/en-us/article/Enable-or-disable-macros-in-Office-documents-7b4fdd2e-174f-47e2-9611-9efe4f860b12
8. Copy all DWG files to the same folder with Excel file. In these DWG files NanoCAD will replace Text according to Data Table of “Replace_Text_In_Blocks_Excel_NanoCAD.xlsm” Excel file.
9. Press “ALT+F8” to run “Replace_Text_In_Blocks_Excel_NanoCAD” macro.
10. Macro will open NanoCAD application and replace all text in DWG blocks and close DWG files
Use AutoCAD commercial version:
1. Download “Replace_Text_In_Blocks_Excel_AutoCAD.xlsm” Excel file with Macro.
2. Open Excel file, press “ALT+F11” to open Microsoft Visual Basic for Application, then “Tools - References”, add:
“AutoCAD YYYY Type Library” file” (acaxXXenu.tlb),
where YYYY – YEAR of your AutoCAD Version, XX – 21, 20, 19,…(version number);
“AutoCAD Focus Control for VBA Type Library” (AcFocusCtrlXX.dll);
“AXDBLib” (axdbXXenu.tlb),
then press OK.
3. Close Microsoft Visual Basic for Application editor.
4. Enable Macros in Excel https://support.office.com/en-us/article/Enable-or-disable-macros-in-Office-documents-7b4fdd2e-174f-47e2-9611-9efe4f860b12
5 Copy all DWG files to the same folder with Excel file. In these DWG files AutoCAD will replace Text according to Data Table of “Replace_Text_In_Blocks_Excel_AutoCAD.xlsm” Excel file.
6. Press “ALT+F8” to run “Replace_Text_In_Blocks_Excel_AutoCAD” macro.
7. Macro will open AutoCAD application and replace all text in DWG blocks and close DWG files
Known features and limitations:
a. Maximum DWG files – 1000.
b. Maximum different blocks quantity to change/insert text - 65530.
c. “Block Name” cell must contain DWG block name (for example, DATASHEET_TITLE, DATE, DESCRIPTION for “Stepping Motor SH4009l1206 Blocks.dwg” file in ZIP archive). “Block Name” could be A$C26677C24, etc.
d. “Data Type” cell could be: “FullText” or “InternalText”.
For «FullText» Type macro will replace text in blocks which consists of Text/MText DWG objects.
For “InternalText” Type macro will replace text between “Text Before Insert” and “Text After Insert” words in blocks with Text. If you clear “Block Name” cell for “InternalText” Type row macro will insert text in all Text/MText objects that are not in blocks.
e. “Value To Replace/Insert” cell must contains text to replace/insert in blocks or Text/MText DWG objects.
f. “Font Height” cell data will change Text Height where macro replaces the text. Cell format for Font height is a Number with decimal separator according to Excel/System settings. https://support.office.com/en-us/article/Change-the-character-used-to-separate-thousands-or-decimals-c093b545-71cb-4903-b205-aebb9837bd1e
For all tables and datasheets which were made as tables of lines, polylines and Text, MText in DWG files you need to manually copy from Text, MText blocks and paste their content to Excel to use data from them. This long process could be automated with Excel file with Macro to create tables of lines, polylines and Text, MText from DWG files to Excel sheets.
There are two options to extract tables of lines, polylines and Text, MText: use free version of NanoCAD or AutoCAD commercial software.
5. Open Excel file, press “ALT+F11” to open Microsoft Visual Basic for Application, then “Tools - References”, add:
“nanoCAD Type Library (NCAuto.dll)”
“OdaX Type Library (OdaX_csd.dll)”,
then press OK.
6. Close Microsoft Visual Basic for Application editor.
7. Enable Macros in Excel https://support.office.com/en-us/article/Enable-or-disable-macros-in-Office-documents-7b4fdd2e-174f-47e2-9611-9efe4f860b12
8. Copy all DWG files to the same folder with Excel file. From these DWG file all tables of lines, polylines and Text, MText will are created on Excel sheets.
9. Press “ALT+F8” to run “Extract_Line_Tables_from_DWG_To_Excel_NanoCAD” macro.
10. Macro will open NanoCAD application and scan all tables of lines, polylines and Text, MText in DWG files. Each created table from DWG file will be placed on a new Excel sheet.
Use AutoCAD commercial version:
1. Download “Extract_Line_Tables_from_DWG_to_Excel_AutoCAD.xlsm” Excel file with Macro.
2. Open Excel file, press “ALT+F11” to open Microsoft Visual Basic for Application, then “Tools - References”, add:
“AutoCAD YYYY Type Library” file” (acaxXXenu.tlb),
where YYYY – YEAR of your AutoCAD Version, XX – 21, 20, 19,…(version number);
“AutoCAD Focus Control for VBA Type Library” (AcFocusCtrlXX.dll);
“AXDBLib” (axdbXXenu.tlb),
then press OK.
3. Close Microsoft Visual Basic for Application editor.
4. Enable Macros in Excel https://support.office.com/en-us/article/Enable-or-disable-macros-in-Office-documents-7b4fdd2e-174f-47e2-9611-9efe4f860b12
5 Copy all DWG files to the same folder with Excel file. From these DWG file all tables of lines, polylines and Text, MText will are created on Excel sheets.
6. Press “ALT+F8” to run “Extract_Line_Tables_from_DWG_To_Excel_AutoCAD” macro.
7. Macro will open AutoCAD application and and scan all tables of lines, polylines and Text, MText in DWG files. Each created table from DWG file will be placed on a new Excel sheet
Known features and limitations:
a. Maximum DWG files – 1000.
b. There are no limitations for table’s quantity for each DWG file or each layout.
c. Each table contour (outline) must be closed.
d. Each cell must be rectangular, not use “stairs” like cells.
e. All text found in the DWG table cell will are placed in Excel cell by vertical order from top to bottom.
f. Tables in DWG files must be draw with lines, old type polylines (2D polylines), Text and MText basic primitives.
e. If your DWG tables are made of blocks with lines, polylines, please explode them all before running the Macro.
f. If your DWG text are made of blocks with Text, MText, please explode them all before running the Macro also.
g. “Extract_Line_Tables_from_DWG_To_Excel” Macro supports creating merged cells found in DWG files.
h. Macro will extract data even there are no some additional SHX fonts installed for DWG file and the text in NanoCAD Free/AutoCAD is shown as “?????????”.
i. All text from DWG files will be copied as “Text” with “Format Cells”
j. All Fonts formatting – Bold, Italic, etc. will be unformatted according to these specifications: https://knowledge.autodesk.com/support/autocad/getting-started/caas/CloudHelp/cloudhelp/2016/ENU/AutoCAD-Core/files/GUID-7D8BB40F-5C4E-4AE5-BD75-9ED7112E5967-htm.html
How to extract tables from DWG files to Excel with NanoCAD Free/AutoCAD
Specialists and engineers are working with DWG files know, that you could not simply extract tables from DWG files and copy their content to Microsoft Excel using Clipboard. For this task please use special Excel file with Macro to import tables from DWG files to Excel sheets.
There are two options to extract: use free version of NanoCAD or AutoCAD commercial software.
Use NanoCAD Free:
1. Download NanoCAD Free http://nanocad.com/page/DownloadNanoCAD
2. Read official NanoCAD installation guide http://nanocad.com/page/InstallationGuide
In PDF format http://nanocad.com/images/InstallGuide/InstallGuide.pdf
Use video guide http://nanocad.com/page/InstallationVideo
3. Don’t forget to register and activate NanoCAD Free.
4. Download “Extract_Tables_From_DWG_to_Excel_NanoCAD.xlsm” Excel file with Macro or “Extract_Tables_From_DWG_to_Excel_97-2003_NanoCAD.xls” for Excel 97-2003.
5. Open Excel file, press “ALT+F11” to open Microsoft Visual Basic for Application, then “Tools - References”, add:
“nanoCAD Type Library (NCAuto.dll)”
“OdaX Type Library (OdaX_csd.dll)”,
then press OK.
6. Close Microsoft Visual Basic for Application editor.
7. Enable Macros in Excel https://support.office.com/en-us/article/Enable-or-disable-macros-in-Office-documents-7b4fdd2e-174f-47e2-9611-9efe4f860b12
8. Copy all DWG files to the same folder with Excel file. From these DWG file all tables will be extracted to Excel sheets.
9. Press “ALT+F8” to run “Extract_Tables_from_DWG_To_Excel_NanoCAD” macro.
10. Macro will open NanoCAD application and extract all tables in DWG files. Each table from DWG file will be placed on a new Excel sheet.
Use AutoCAD commercial version:
1. Download “Extract_Tables_from_DWG_to_Excel_AutoCAD.xlsm” Excel file with Macro or “Extract_Tables_from_DWG_to_Excel_97-2003_AutoCAD.xls” for Excel 97-2003.
2. Open Excel file, press “ALT+F11” to open Microsoft Visual Basic for Application, then “Tools - References”, add:
“AutoCAD YYYY Type Library” file” (acaxXXenu.tlb),
where YYYY – YEAR of your AutoCAD Version, XX – 21, 20, 19,…(version number);
“AutoCAD Focus Control for VBA Type Library” (AcFocusCtrlXX.dll);
“AXDBLib” (axdbXXenu.tlb),
then press OK.
3. Close Microsoft Visual Basic for Application editor.
4. Enable Macros in Excel https://support.office.com/en-us/article/Enable-or-disable-macros-in-Office-documents-7b4fdd2e-174f-47e2-9611-9efe4f860b12
5. Copy all DWG files to the same folder with Excel file. From these DWG file all tables will be extracted to Excel sheets.
6. Press “ALT+F8” to run “Extract_Tables_from_DWG_To_Excel_AutoCAD” macro.
7. Macro will open AutoCAD application and extract all tables in DWG files. Each table from DWG file will be placed on a new Excel sheet.
For some purposes IT specialists, for example, could be needed to obtain base with passwords to use them in their networks, domains, work groups, etc. So, we present Excel based password generator powered by VBA macro.
Passwords Generator Free-Excel-VBA.Blogspot.com
Password Generator user need to change quantity (15 as shown in a screen), password length (from 1 to 255), and symbols to use in your passwords listings. Then Press "Alt+F8", select "Passwords_Generator" macro and then press "Run" button. Maximum passwords: 65530, maximum password length: 255 symbols. Default symbols:
!"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~
You can change, add, modify symbols string - your passwords will be generated with them only.
If there will are some errors, macro will generate 25 passwords with 8 symbols in each.
For blank symbols string macro will use "0123456789" string.
There are no hidden activities, such as password "stealing", sending, accumulating. You can view source code by pressing "Alt+F11" to check it.
Macro compatibility: MS Excel 97, 2000, 2003, 2007, 2010, 2013