New Forum Category
Moderators: Jason Susnjara, Larry Epplin, Clint Buechlein, Scott G Vaal
-
- Guru Member
- Posts: 838
- Joined: Sat, Mar 05 2011, 7:29AM
- Company Name: Heritage WoodWorks
- Country: UNITED STATES
- Location: Jacksonville Nc
- Contact:
New Forum Category
I was wandering if anyone would be interested in an addition to this forum for Excel users seeing as how eCabinets offers imports and exports using this program. Maybe it would benefit every one to exchange info. on how they use excel and eCabinets in there day to day business and production strategies. Maybe it could be called something like eCabinets and excel?
Just a thought
Just a thought
Yes! Oh Yes! I Think We Are Going to make It!
https://www.youtube.com/user/ecabinetstips
https://www.facebook.com/groups/397563664342467
https://www.youtube.com/user/ecabinetstips
https://www.facebook.com/groups/397563664342467
-
- New Member
- Posts: 17
- Joined: Thu, May 12 2011, 6:38AM
- Company Name: Northlander Industries
- Country: CANADA
Re: New Forum Category
Yes, I would be interested in this topic.
I currently use Excel spreadsheets to upload repetative batches of cabinets. I also Export the CNC batches so that our costing department can verify how each cabinet is built and make sure they are costing the material output correctly.
Is there other ways to use Excel?
Thanks
I currently use Excel spreadsheets to upload repetative batches of cabinets. I also Export the CNC batches so that our costing department can verify how each cabinet is built and make sure they are costing the material output correctly.
Is there other ways to use Excel?
Thanks
-
- Guru Member
- Posts: 838
- Joined: Sat, Mar 05 2011, 7:29AM
- Company Name: Heritage WoodWorks
- Country: UNITED STATES
- Location: Jacksonville Nc
- Contact:
Re: New Forum Category
We use it to simplify the cut list that eCabinets generates. I am working on a program that will import door sizes and export door panels sizes for flat panel doors and mdf doors to batch for machining of witch requires quite a bit of VBA code to make it user friendly. And also import Spread sheets into Line drawings
Yes! Oh Yes! I Think We Are Going to make It!
https://www.youtube.com/user/ecabinetstips
https://www.facebook.com/groups/397563664342467
https://www.youtube.com/user/ecabinetstips
https://www.facebook.com/groups/397563664342467
-
- Senior Member
- Posts: 114
- Joined: Thu, Dec 29 2005, 3:14AM
- Location: Perth Western Australia
Re: New Forum Category
Hi I would be interested to learn how excel can simplify ecabs cutlists.
Nearly is not enough
-
- Guru Member
- Posts: 838
- Joined: Sat, Mar 05 2011, 7:29AM
- Company Name: Heritage WoodWorks
- Country: UNITED STATES
- Location: Jacksonville Nc
- Contact:
Re: New Forum Category
Here are some screen shots of eCabinets BoardStockListing before and after Modifying in Excel
Yes! Oh Yes! I Think We Are Going to make It!
https://www.youtube.com/user/ecabinetstips
https://www.facebook.com/groups/397563664342467
https://www.youtube.com/user/ecabinetstips
https://www.facebook.com/groups/397563664342467
-
- Senior Member
- Posts: 199
- Joined: Sat, Jul 29 2006, 9:11AM
- Company Name: Classic Custom Wood
- Location: NC Iowa
- Contact:
Re: New Forum Category
A lot of my jobs come in on an excel spreadsheet. I've built macros to format that info and then I import into ecabs as a batch file. It even selects the right directory depending on door overlay, etc.
After ecabs has worked it's magic and I've made any necessary modifications I save, create the twd and export the cutlist. After a bunch more macros, I've got labels and code to cut rails and stiles , door and face frames, on my CNC chop saw. I also have door panel sizes which I can import pack in for flat panel doors for thee machine to cut. Seems that there are other things it does, but that's all I can think of for now.
This whole excel - ecabs combination has greatly decreased time and virtually eliminated errors.
After ecabs has worked it's magic and I've made any necessary modifications I save, create the twd and export the cutlist. After a bunch more macros, I've got labels and code to cut rails and stiles , door and face frames, on my CNC chop saw. I also have door panel sizes which I can import pack in for flat panel doors for thee machine to cut. Seems that there are other things it does, but that's all I can think of for now.
This whole excel - ecabs combination has greatly decreased time and virtually eliminated errors.
-
- Guru Member
- Posts: 838
- Joined: Sat, Mar 05 2011, 7:29AM
- Company Name: Heritage WoodWorks
- Country: UNITED STATES
- Location: Jacksonville Nc
- Contact:
Re: New Forum Category
Thank you all for the input. This is exactly what I was speaking of when I suggested this addition to the forum. This could also be a place for excel and eCabinets users to help each other with those macros such as Ray is talking about. There are a lot of forums out there not to mention videos that are extremely helpful in learning VBA but there are very few viewers that visit these sights that use excel for woodworking and cabinet construction as well as eCabinets combined with excel.
Yes! Oh Yes! I Think We Are Going to make It!
https://www.youtube.com/user/ecabinetstips
https://www.facebook.com/groups/397563664342467
https://www.youtube.com/user/ecabinetstips
https://www.facebook.com/groups/397563664342467
-
- Guru Member
- Posts: 838
- Joined: Sat, Mar 05 2011, 7:29AM
- Company Name: Heritage WoodWorks
- Country: UNITED STATES
- Location: Jacksonville Nc
- Contact:
Re: New Forum Category
Just in case anyone is interested here is a link to a YouTube sight that I came across a few months ago. I have ben trying to learn VBA for a couple of years now only to have gained very basic knowledge at best. After watching this Guys videos I have gone from righting approximately 5% of my own code to approximately 90%.The way he explains the basics helps to make this language make sense. Before I was relying on recording macros and searching the forums and copying bits and pieces of code then trying to put it all together on a regular basses.
http://www.youtube.com/user/ExcelVbaIsFun?feature=watch
http://www.youtube.com/user/ExcelVbaIsFun?feature=watch
Yes! Oh Yes! I Think We Are Going to make It!
https://www.youtube.com/user/ecabinetstips
https://www.facebook.com/groups/397563664342467
https://www.youtube.com/user/ecabinetstips
https://www.facebook.com/groups/397563664342467
-
- Senior Member
- Posts: 199
- Joined: Sat, Jul 29 2006, 9:11AM
- Company Name: Classic Custom Wood
- Location: NC Iowa
- Contact:
Re: New Forum Category
Just so clarify a little about macros and VB. Macros are easy - you just click a button that says record. When you have done what you want you click stop recording. There are intelligent functions that macros can't do - Find the end of data as an example. This is where you need VB. I know nothing more about this programing language than to know I need it for certain things.
I found a site called Just Ask. My guess - these are a group of folks who are freelancers or extra income programmers. You just ask you question, post the file for an example if necessary, and with in an hr. or so you have the script. Beats me trying for hrs. to try to figure it out!!!! My cost has been $45.00/ question - you can leave a tip. It's an awfully cheap way for me to have the programing help when I need it.
So don't be scared of excel and macro's. it's a pretty intuitive program that's been around for years and now you know where to get the help for those things you can't do.
As a side note: Once I found that icon in ecabs batch section that says - IMPORT FROM EXCEL - life has been good. Too bad it only took 7 years to see it!!!!!
I found a site called Just Ask. My guess - these are a group of folks who are freelancers or extra income programmers. You just ask you question, post the file for an example if necessary, and with in an hr. or so you have the script. Beats me trying for hrs. to try to figure it out!!!! My cost has been $45.00/ question - you can leave a tip. It's an awfully cheap way for me to have the programing help when I need it.
So don't be scared of excel and macro's. it's a pretty intuitive program that's been around for years and now you know where to get the help for those things you can't do.
As a side note: Once I found that icon in ecabs batch section that says - IMPORT FROM EXCEL - life has been good. Too bad it only took 7 years to see it!!!!!
-
- Senior Member
- Posts: 199
- Joined: Sat, Jul 29 2006, 9:11AM
- Company Name: Classic Custom Wood
- Location: NC Iowa
- Contact:
Re: New Forum Category
Scott,
Just watched a couple of the VBA videos. Very good - now I can at least start to understand what's happening when I look at the code. I'll continue with this series. Thanks
Just watched a couple of the VBA videos. Very good - now I can at least start to understand what's happening when I look at the code. I'll continue with this series. Thanks
-
- Guru Member
- Posts: 838
- Joined: Sat, Mar 05 2011, 7:29AM
- Company Name: Heritage WoodWorks
- Country: UNITED STATES
- Location: Jacksonville Nc
- Contact:
Re: New Forum Category
Thank you Ray. I Think that eCabinets has so much info that can be unleashed with its ability to export and import data to and from excel. But it is unfortunate that there are so many eCabinet users that are intimidated by the results that they get when pressing that Export to excel button.
Yes! Oh Yes! I Think We Are Going to make It!
https://www.youtube.com/user/ecabinetstips
https://www.facebook.com/groups/397563664342467
https://www.youtube.com/user/ecabinetstips
https://www.facebook.com/groups/397563664342467
-
- Senior Member
- Posts: 199
- Joined: Sat, Jul 29 2006, 9:11AM
- Company Name: Classic Custom Wood
- Location: NC Iowa
- Contact:
Re: New Forum Category
Agreed. I never used the cut list for quite some time and then only for very limited things. There was to much info that I didn't want. I had used excel and macros many years in the past so when I recognized the opportunity one thing lead to another.
The Key to the cut list is to capitalize on the info that is contained in it and disregard what you don't want. Every user will want something sorted differently. As an example: I sort the cut list for FF part by cabinet, that way I don't have to sort the parts later. I have a friend I talk to regularly that sorts longest to shortest to max material, and then sorts. Well, I'll tell ya he's wrong and I'm right!
The ecabs team will never please every one. That's where the cut list can come in. Each user can manipulate the data to suit there wishes. The numbers are there, it's just getting them into a workable form, whether it be on screen - a printout - file to print labels - code to send to a chop saw stop. The #'s are there. Use them.
The forum heading should be something like "leverage ecabs with excel". Maybe a section on "how to use excel". This doesn't have to be reinventing the wheel. Just question and links to what is already on the web. Sometimes the searches don't work so good when you don't know the words to use.
Another section of macros to download. Sort FF by length - sort FF by cabinet, as an example.
The best would be to see what Scott is doing with it. My bet is it's something I haven't thought of!
The Key to the cut list is to capitalize on the info that is contained in it and disregard what you don't want. Every user will want something sorted differently. As an example: I sort the cut list for FF part by cabinet, that way I don't have to sort the parts later. I have a friend I talk to regularly that sorts longest to shortest to max material, and then sorts. Well, I'll tell ya he's wrong and I'm right!
The ecabs team will never please every one. That's where the cut list can come in. Each user can manipulate the data to suit there wishes. The numbers are there, it's just getting them into a workable form, whether it be on screen - a printout - file to print labels - code to send to a chop saw stop. The #'s are there. Use them.
The forum heading should be something like "leverage ecabs with excel". Maybe a section on "how to use excel". This doesn't have to be reinventing the wheel. Just question and links to what is already on the web. Sometimes the searches don't work so good when you don't know the words to use.
Another section of macros to download. Sort FF by length - sort FF by cabinet, as an example.
The best would be to see what Scott is doing with it. My bet is it's something I haven't thought of!
-
- Guru Member
- Posts: 838
- Joined: Sat, Mar 05 2011, 7:29AM
- Company Name: Heritage WoodWorks
- Country: UNITED STATES
- Location: Jacksonville Nc
- Contact:
Re: New Forum Category
Ok here is some code that will create a cabinet size report from the SheetComponetLising Worksheet.You can insert a new module in your personal workbook and in the properties name it what ever you wish. Copy this code and paste it in this module. In ecabinets Cut list click on export to excel and click open. Then in the developer tab click on macros find the Module that you just created and click run.
I am not or do I claim to be a advanced Vba expert so if anyone has any advice on how this code can be improve upon please post them. Good of bad
Also always test new code on a workbook that you do not care if it gets screwed up. What works for me may not work for you!!!!
I am not or do I claim to be a advanced Vba expert so if anyone has any advice on how this code can be improve upon please post them. Good of bad
Also always test new code on a workbook that you do not care if it gets screwed up. What works for me may not work for you!!!!
Code: Select all
Sub CabinetSizeReport()
'=========================================================================================
'The Following Code will extract the Cabinet Sizes from The SheetComponetListing Worksheet
'and put them on a new worksheet named CabinetSizeReport.
'=========================================================================================
'Inform The user about this macro
MsgBox "This Macro will Insert a new WorkSheet Named CabinetSizeReport and extract the cabinet Numbers, Cabinet sizes and Type of Cabinet from the SheetComponetListing worksheet. PLEASE NOTE THAT SOME ASSEMBLIES MAY BE LISTED MULTABLE TIMES ALSO SOME CABINETS OR ASSEMBLIES MAY NOT USE SHEET STOCK IN THEIR CONSTRUCTION THEREFORE THESE CABINETS WILL NOT BE LISTED IN THIS REPORT"
'==============================================================================================
'Loop through the worksheets to check and see if thie CabinetSizeReport worksheet already exist
'==============================================================================================
Application.ScreenUpdating = False
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
If ws.Name = "CabinetSizeReport" Then
GoTo ImportData
Else: End If
Next
'==============================================
'Add The CabinetSize Worksheet To This workbook
'==============================================
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = "CabinetSizeReport"
ImportData:
'Variables
Dim ShtCompLst As Worksheet
Set ShtCompLst = ActiveWorkbook.Sheets("SheetComponentListing")
Dim Sht2 As Worksheet
Set Sht2 = ActiveWorkbook.Sheets("CabinetSizeReport")
'Find The Last Row For Sheet2
If Sht2.Cells(Rows.Count, 1).End(xlUp).Row = 1 Then
Lr = 2
Else
Lr = Sht2.Cells(Rows.Count, 1).End(xlUp).Row
End If
'Clear any existing Data From Ws
Sht2.Activate
Sht2.Columns.EntireColumn.ClearContents
'Select all data from The SheetComponentListing Ws To Copy
'Find The Last row For The SheetComponetListing Ws
If ShtCompLst.Cells(Rows.Count, 1).End(xlUp).Row = 1 Then
ShtCompLr = 2
Else
ShtCmpLr = ShtCompLst.Cells(Rows.Count, 1).End(xlUp).Row
End If
ShtCompLst.Activate
ShtCompLst.Range("A1:H" & ShtCmpLr).Copy
'Paste The Data To Sheet2
Sht2.Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Delete Unwanted Columns
Sht2.Range("B1:G" & Lr).EntireColumn.Delete
'Remove Duplicates
Columns("A:B").Select
ActiveSheet.Range("A:B").RemoveDuplicates Columns:=2, Header:=xlYes
Range("A2").Select
'TextToColumns (Put Cabinet Number In its Own Column)
Sht2.Columns("B:B").Select
Selection.TextToColumns Destination:=Range("B1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(17, 1)), TrailingMinusNumbers:=True
'Get Rid Of The - In The Cabinet Name Column
If Sht2.Cells(Rows.Count, 1).End(xlUp).Row = 1 Then
Lr = 2
Else
Lr = Sht2.Cells(Rows.Count, 1).End(xlUp).Row
End If
For x = 2 To Lr
Cells(x, 2).Activate
ActiveCell.Replace What:="-", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next x
'AutoFit Columns and Rows
Sht2.Columns.AutoFit
Sht2.Rows.AutoFit
Sht2.Range("A1").Select
'==========================================================================================
'Get Rid Of The Cabinet Names and Leave Just The Cabinet Sizes in Column C
'==========================================================================================
If Sht2.Cells(Rows.Count, 1).End(xlUp).Row = 1 Then
Lr = 2
Else
Lr = Sht2.Cells(Rows.Count, 1).End(xlUp).Row
End If
For x = 2 To Lr
Pos1 = InStr(1, Sht2.Range("C" & x).Value, " ")
On Error Resume Next
Range("D" & x).Value = Mid(Sht2.Range("C" & x).Value, Pos1 + 3)
'========================================================================
'Filter out the Assemblies
'========================================================================
Pos1 = InStr(1, Sht2.Range("C" & x).Value, "Assembly Cab. No.")
On Error Resume Next
Range("D" & x).Value = Mid(Sht2.Range("C" & x).Value, Pos1 + 0)
Next x
'==========================================================================
'Remove The Assemblies Name
'==========================================================================
For x = 2 To Lr
Pos2 = InStr(1, Sht2.Range("D" & x).Value, " ")
On Error Resume Next
Range("D" & x).Value = Mid(Sht2.Range("D" & x).Value, Pos2 + 0)
Next x
'Delete Column C
Sht2.Range("C1").EntireColumn.Delete
'Remove Wrap Text
Columns("A:C").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
.WrapText = False
End With
'Get Rid of the Tripple Blank spaces
If Sht2.Cells(Rows.Count, 1).End(xlUp).Row = 1 Then
Lr = 2
Else
Lr = Sht2.Cells(Rows.Count, 1).End(xlUp).Row
End If
For x = 2 To Lr
Cells(x, 3).Activate
ActiveCell.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next x
'=====================================================
'Put the cabinet sizes and type into there own columns
'=====================================================
'Seperate Cabinet Width
Columns("C:C").Select
Selection.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="""", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), _
TrailingMinusNumbers:=True
'Seperate Cabinet Height
Columns("D:D").Select
Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="X", FieldInfo:=Array(Array(1, 9), Array(2, 1)), TrailingMinusNumbers:=True
'Seperate Cabinet Height
Columns("E:E").Select
Selection.TextToColumns Destination:=Range("E1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="X", FieldInfo:=Array(Array(1, 9), Array(2, 1)), TrailingMinusNumbers:=True
'Seperate Cabinet Type
Columns("F:F").Select
Selection.TextToColumns Destination:=Range("F1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="(", FieldInfo:=Array(Array(1, 9), Array(2, 1)), TrailingMinusNumbers:=True
'Remove the ) in the cabinet type
Cells.Replace What:=")", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
'Put the column headers in the new columns
Sht2.Range("C1") = "Width"
Sht2.Range("D1") = "Height"
Sht2.Range("E1") = "Depth"
Sht2.Range("F1") = "Type"
'AutoFit Columns and Rows
Sht2.Columns.AutoFit
Sht2.Rows.AutoFit
Sht2.Range("A1").Select
'====================================================================================================
'Loop Through the Qty,Width,Hight,Depth Columns and Make sure that the Numbers are not stored as Text
'====================================================================================================
If Sht2.Cells(Rows.Count, 1).End(xlUp).Row = 1 Then
Lr = 2
Else
Lr = Sht2.Cells(Rows.Count, 1).End(xlUp).Row
End If
For x = 2 To Lr
Cells(x, 1).Activate
ActiveCell.Value = (ActiveCell.Value + 0)
Cells(x, 3).Activate
ActiveCell.Value = (ActiveCell.Value + 0)
Cells(x, 4).Activate
ActiveCell.Value = (ActiveCell.Value + 0)
Cells(x, 5).Activate
ActiveCell.Value = (ActiveCell.Value + 0)
Next x
Application.ScreenUpdating = True
End Sub
Yes! Oh Yes! I Think We Are Going to make It!
https://www.youtube.com/user/ecabinetstips
https://www.facebook.com/groups/397563664342467
https://www.youtube.com/user/ecabinetstips
https://www.facebook.com/groups/397563664342467
-
- Guru Member
- Posts: 838
- Joined: Sat, Mar 05 2011, 7:29AM
- Company Name: Heritage WoodWorks
- Country: UNITED STATES
- Location: Jacksonville Nc
- Contact:
Re: New Forum Category
Ok. Some of you may be thinking so how would this code help me.
I came up with this code to use in my Finish Calculation workbook I just modified it so that it can be put into a module in the personal workbook
Before I came up with this macro I had to either look through the paper work and type each cabinet size line by line.
Or toggle back and forth from excel to the eCabinet cultist again typing each cabinet size line by line.
This proses would take from 10 to 30 minuets for me to do.
As you can see in the video. What would have taken me 15 minuets took just a few seconds
I guess the point that I am trying to make is that If this category was available then users could ask for help on how to accomplish task like this.
That is of Corse if there are any eCabinet members out there that are willing to lend there help
http://screencast.com/t/0n0QjpgNLuc
I came up with this code to use in my Finish Calculation workbook I just modified it so that it can be put into a module in the personal workbook
Before I came up with this macro I had to either look through the paper work and type each cabinet size line by line.
Or toggle back and forth from excel to the eCabinet cultist again typing each cabinet size line by line.
This proses would take from 10 to 30 minuets for me to do.
As you can see in the video. What would have taken me 15 minuets took just a few seconds
I guess the point that I am trying to make is that If this category was available then users could ask for help on how to accomplish task like this.
That is of Corse if there are any eCabinet members out there that are willing to lend there help
http://screencast.com/t/0n0QjpgNLuc
Yes! Oh Yes! I Think We Are Going to make It!
https://www.youtube.com/user/ecabinetstips
https://www.facebook.com/groups/397563664342467
https://www.youtube.com/user/ecabinetstips
https://www.facebook.com/groups/397563664342467
-
- Senior Member
- Posts: 199
- Joined: Sat, Jul 29 2006, 9:11AM
- Company Name: Classic Custom Wood
- Location: NC Iowa
- Contact:
Re: New Forum Category
Scott,
Nice example of the power available. Un less I missed it, I would include a calc for quantity used and cost. I'd sure contribute. I am sure others would have things that they want. After hearing what they want I'll bet I'd be saying why didn't I think of that?"
Nice example of the power available. Un less I missed it, I would include a calc for quantity used and cost. I'd sure contribute. I am sure others would have things that they want. After hearing what they want I'll bet I'd be saying why didn't I think of that?"