It's really amazing how random that inventor excel export winds up, so I have reluctantly added MMM's column sorting code into my export "program":
'heavily reformat exported xls...
'On Error Resume Next
Set excel_app = CreateObject("Excel.Application")
'excel_app.Visible = True
excel_app.Workbooks.Open (bomname)
Dim rows As Integer
Dim rowbottom As String
rows = excel_app.ActiveSheet.UsedRange.rows.Count
rowbottom = "A1" & ":" & "A" & rows
excel_app.Range("A1").EntireColumn.Insert
excel_app.Range(rowbottom) = "=TRIM(LEFT(SUBSTITUTE(MID(CELL(""filename"",A1),FIND(""["",CELL(""filename"",A1))+1,255),"".xl"",REPT("" "",255)),255))"
excel_app.Range("F1").EntireColumn.Insert
excel_app.Range("F1") = "Assembly Qty"
excel_app.Range("A1") = "Order"
'try some sorting
Dim arrColOrder
Dim ndx As Integer
Dim Found As Range
Dim counter As Integer
arrColOrder = Array("Order", "Item", "Part Number", "BOM Structure", "QTY", "Assembly Qty", "A1 Router", "A1 Time", "B1 Laser", "B1 Time", "C1 Saw-AL", "C1 Time", "D1 Saw-ST", "D1 Time", "E1 C-Axis", "E1 Time", "F1 Machining", "F1 Time", "G1 Debur", "G1 Time", "H1 Brake", "H1 Time", "I1 Weld", "I1 Time", "J1 RWeld", "J1 Time", "K1 Assembly", "K1 Time", "L1 Outsource", "L1 Time", "M1 Ship", "Bulk", "Setup")
counter = 1
On Error Resume Next
For ndx = LBound(arrColOrder) To UBound(arrColOrder)
'was not successful setting global variable for worksheet, tried some bfi:
'Set Found = Sheet1.rows("1:1").Find(arrColOrder(ndx), , LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
Set Found = Sheet1.rows("1:1").Find(arrColOrder(ndx), , -4163, 1, 2, 1, False)
If Err.Number <> 0 Then
MsgBox ("Error With Excel FIND function: " & Err.Number & " :: " & Err.Description & vbLf & vbLf & ndx)
Err.Clear
End If
If Not Found Is Nothing Then
If Found.Column <> counter Then
Found.EntireColumn.Cut
xlws.Columns(counter).Insert -4161
Application.CutCopyMode = False
End If
counter = counter + 1
End If
Next
If Err.Number <> 0 Then
MsgBox ("Reorder Columns Rule Error: " & Err.Number & " :: " & Err.Description & vbLf & vbLf & ndx)
Err.Clear
End If
excelApp.Columns.AutoFit
excel_app.ActiveWorkbook.Save
excel_app.ActiveWorkbook.Close
I believe if I roll back to xlws in this code instead of "Sheet1" and Public xlws as string, xlws = Sheet1, this would work, but I don't know where to declare these global variables!!
When this runs at best, I get error with excel find 424: object required - obviously the arrayed names aren't being passed.
I tried making a new otherwise empty module where I set xlws as a public string, that works, gets me a different error of invalid qualifier for the set found = xlws.rows line. I can dim xlws as Sheet1 inside of the sub, get the same result.
I'm sure I'll learn something from this... Thanks everyone.