none
urgent bom explosion vba code RRS feed

  • Question

  • Hello

    I need to be able to explode  final assembly  demand through a Bill of Material of bicycle and wheel to obtain as output only the sub parts of bicycle with multiplying the sub parts of wheel by 2 to have the final assembly of a bicycle.

    Hopefully the tables below help explain the inputs I have and the outputs required.

    Regards,

    input file

    part subpart               quantity
    Bicycle Handlebar 1
    Bicycle  Pedal 2
    Bicycle  Frame 1
    Bicycle Wheels 2
    Bicycle Saddle 1
    Wheel Tire 1
    Wheel Spoke 32
    Wheel hub 1

    output

    quantity subpart
    1 Handlebar
    2  Pedal
    1  Frame
    2 Wheels
    1 Saddle
    2 Tire
    64 Spoke
    2 hub

    Sunday, February 19, 2017 6:56 PM

Answers

  • Hi Cecilia_db,

    you can refer code below to take input from file and generate output file.

    Sub demo()
    Dim MyFile As String
    MyFile = Application.GetOpenFilename()
    Dim wb1, wb2 As Workbook
    Set wb1 = Workbooks.Open(MyFile)
    
    Set wb2 = Workbooks.Add
    wb2.SaveAs ("C:\Users\v-padee\Desktop\output3.xlsx")
    
    Dim sht1 As Worksheet
    Set sht1 = wb1.Sheets("Sheet1")
    Dim quantity As Integer
    quantity = sht1.Cells(2, 4).Value
    wb2.Sheets(1).Cells(1, 1).Value = "Sub part"
    wb2.Sheets(1).Cells(1, 2).Value = "Quantity"
    Dim i As Long
    For i = 2 To 9
    wb2.Sheets(1).Cells(i, 1).Value = sht1.Cells(i, 2).Value
    wb2.Sheets(1).Cells(i, 2).Value = sht1.Cells(i, 3).Value * quantity
    Next i
    
    wb2.Save
    wb1.Close
    wb2.Close
    End Sub
    

    input file:

    Output file:

    you can change the output path as per your requirement.

    this is just an example to give you an idea how you can fulfil your requirement.

    further you can modify it by yourself.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Cecilia_db Wednesday, February 22, 2017 9:32 AM
    Tuesday, February 21, 2017 6:05 AM
    Moderator

All replies

  • Hi Cecilia_db,

    I am little confused, as I don't know in which file format input file is?

    but from your output description, you want to find the total number of parts needs to build the bicycle.

    if you want to find that then you not need for any input file.

    you can develop a user form, that will show you the quantity of parts needed if you enter the number of bicycle you want to build.

    code on button click of user form.

    Private Sub CommandButton1_Click()
    Dim i As Integer
    i = Me.TextBox1.Text
    Me.TextBox2.Text = i * 1
    Me.TextBox3.Text = i * 2
    Me.TextBox4.Text = i * 1
    Me.TextBox5.Text = i * 2
    Me.TextBox6.Text = i * 1
    Me.TextBox7.Text = i * 2
    Me.TextBox8.Text = i * 32
    Me.TextBox9.Text = i * 2
    End Sub
    

    Output:

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Cecilia_db Monday, February 20, 2017 9:30 AM
    • Unmarked as answer by Cecilia_db Monday, February 20, 2017 9:30 AM
    Monday, February 20, 2017 2:35 AM
    Moderator
  • hello,

    thank your very much  for your reply 

    the input file is an excel table and from which we have the output not a user form .please have you any idea about how to code to obtain the output file as an output.

    thanks in advance.

    regards,

    Monday, February 20, 2017 9:36 AM
  • Cecilla,
    re:  bill of materials

    You can download a simple bill of materials template (free) from here...
      https://www.vertex42.com/ExcelTemplates/bill-of-materials.html

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)
    Tuesday, February 21, 2017 1:08 AM
  • Hi Cecilia_db,

    you can refer code below to take input from file and generate output file.

    Sub demo()
    Dim MyFile As String
    MyFile = Application.GetOpenFilename()
    Dim wb1, wb2 As Workbook
    Set wb1 = Workbooks.Open(MyFile)
    
    Set wb2 = Workbooks.Add
    wb2.SaveAs ("C:\Users\v-padee\Desktop\output3.xlsx")
    
    Dim sht1 As Worksheet
    Set sht1 = wb1.Sheets("Sheet1")
    Dim quantity As Integer
    quantity = sht1.Cells(2, 4).Value
    wb2.Sheets(1).Cells(1, 1).Value = "Sub part"
    wb2.Sheets(1).Cells(1, 2).Value = "Quantity"
    Dim i As Long
    For i = 2 To 9
    wb2.Sheets(1).Cells(i, 1).Value = sht1.Cells(i, 2).Value
    wb2.Sheets(1).Cells(i, 2).Value = sht1.Cells(i, 3).Value * quantity
    Next i
    
    wb2.Save
    wb1.Close
    wb2.Close
    End Sub
    

    input file:

    Output file:

    you can change the output path as per your requirement.

    this is just an example to give you an idea how you can fulfil your requirement.

    further you can modify it by yourself.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Cecilia_db Wednesday, February 22, 2017 9:32 AM
    Tuesday, February 21, 2017 6:05 AM
    Moderator
  • thank you very much 
    Wednesday, February 22, 2017 9:33 AM