New Forum Category

[Scott Marshburn]

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

[Brenda Hallman]

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

[Scott Marshburn]

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

[John Powell]

Hi I would be interested to learn how excel can simplify ecabs cutlists.

[Scott Marshburn]

Here are some screen shots of eCabinets BoardStockListing before and after Modifying in Excel

[Ray Jorgensen]

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.

[Scott Marshburn]

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.

[Scott Marshburn]

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

[Ray Jorgensen]

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!!!!!

[Ray Jorgensen]

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

[Scott Marshburn]

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.

[Ray Jorgensen]

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!

[Scott Marshburn]

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!!!!

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

[Scott Marshburn]

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

[Ray Jorgensen]

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?"