Adding time and counting columns.

[haitran]

Hi everyone, I have a macro that saves selected columns from a Sheet to a new Workbook and save it as Text file. I have a few things that I want to add to the macro and not sure how should I name the Title of this thread, please forgive me if you misunderstood something.

Here is a few things I want to add but not sure how to start. I want to add 2 mandatory columns name COI and Workpackage.These 2 columns have to be always in the chosen columns. They are not from the "Input Sheet". Column COI is just the time create the new Workbook,for exp: a cell of column COI will look like this "COI (12/21 13:03)". Column Workpackage is a counting column for each row with format main. + number. For exp: main.1, main.2...

Any help would be appreciated.
Thank you very much.

P/s: I want to attach my current macro but I can't seem to find a way to do that. Anyone know how to? Thank you.

 

[NdNoviceHlp]

Hi haitran and Welcome to the Board. U can copy and paste the macro to the thread. Your needs are unclear. U need to specify exact column and row needs as well as the new file path. Please use code tags... "["code"]" your macro "["/code"]" (remove all the parenthesis). HTH. Dave

 

[haitran]

Hi haitran and Welcome to the Board. U can copy and paste the macro to the thread. Your needs are unclear. U need to specify exact column and row needs as well as the new file path. Please use code tags... "["code"]" your macro "["/code"]" (remove all the parenthesis). HTH. Dave

Thank you for your reply.
So basically I have a macro that saves selected columns from a Sheet to a new Workbook and save it as a text file. There are 2 Sheets, Settings and Inputfile.

Sheet Inputfile has data with column names for each column.

Sheet Settings has some settings that user will enter their input to save desired columns. User will enter columns they want to save to Column A,then they can put them in any order they want in "Output order" (row 10).

Here is the macro.

Sub save_txt()
    Dim rngC As Range
    Dim rngF As Range
    Dim wbNew As Workbook
    Dim shtNew As Worksheet
    Dim shtIF As Worksheet
    Dim shtSet As Worksheet
    'Dim headerRowNumber As Integer
    Dim wbText As Workbook
    Dim LastRow As Long
    Dim Cell As Range ' cell in text file
    Dim MaxLen As Long ' maximum string length for text file
    
    
    Set shtSet = ThisWorkbook.Worksheets("Settings")
    Set shtIF = ThisWorkbook.Worksheets("Inputfile")
    Set wbNew = Workbooks.Add
    Set shtNew = wbNew.Worksheets(1)
    shtNew.Name = "Extracted Values"
    'headerRowNumber = Sheets("Settings").Cells(1, 5).Value
    
    With shtSet
        For Each rngC In .Range(.Range("E10"), .Cells(10, .Columns.Count).End(xlToLeft))
            Set rngF = shtIF.Cells.Find(rngC.Value)
            If Not rngF Is Nothing Then
                shtIF.Cells(18, rngF.Column).Resize(shtIF.UsedRange.Rows.Count - 9).Copy shtNew.Columns(rngC.Column - 4)
            End If
        Next rngC
    End With
    
    shtNew.UsedRange.EntireColumn.AutoFit
     
    wbNew.SaveAs Application.GetSaveAsFilename("New File.xlsx", "Excel File (*.xlsx),*.xlsx"), FileFormat:=xlOpenXMLWorkbook
    
    wbNew.SaveAs ThisWorkbook.Path & Application.PathSeparator & "Converter.txt", FileFormat:=xlCSV, CreateBackup:=False
    
    If Not IsNumeric(ThisWorkbook.Worksheets(1).Range("ShortenOption")) Then
      MsgBox "Value for Shorten Option not valid, text not truncated."
    Else
       MaxLen = ThisWorkbook.Worksheets(1).Range("ShortenOption")
       Set wbText = Workbooks("Converter.txt")
       With wbText.Worksheets(1)
         For Each Cell In .UsedRange
            If Len(Cell.Value) > MaxLen Then
               Cell = Left(Cell.Value, MaxLen) & "...."
            End If
         Next Cell
      End With
   End If
   
   wbText.Save
    wbNew.SaveAs ThisWorkbook.Path & Application.PathSeparator & "Converter_shorten.txt", FileFormat:=xlCSV, CreateBackup:=False
    'wbNew.SaveAs Application.GetSaveAsFilename("Newfile.txt"), FileFormat:=xlText, CreateBackup:=False
End Sub

So I want to add 2 more columns, COI and WorkPackage. These 2 columns have to be always in the chosen columns (Column A). They are not from the "Input Sheet". Column COI is just the time the new Workbook created,for exp: all the cells of column COI will look like this "COI (12/21 13:03)". Column Workpackage is a counting column for each row with format main. + number. For exp: main.1, main.2...

Hope you understand my situation. Appreciate any help. Thank you very much.

 

[NdNoviceHlp]

So do U want something in this changed....

With shtSet
        For Each rngC In .Range(.Range("E10"), .Cells(10, .Columns.Count).End(xlToLeft))
            Set rngF = shtIF.Cells.Find(rngC.Value)
            If Not rngF Is Nothing Then
                shtIF.Cells(18, rngF.Column).Resize(shtIF.UsedRange.Rows.Count - 9).Copy shtNew.Columns(rngC.Column - 4)
            End If
        Next rngC
    End With
    
    shtNew.UsedRange.EntireColumn.AutoFit

It seems like its' made to adjust for adding/removing columns. Do U want code to insert columns or re-arrange them? When sheet settings are done this should be where the input file structure is created. Maybe a bit more info. Dave

 

[haitran]

So do U want something in this changed....

With shtSet
        For Each rngC In .Range(.Range("E10"), .Cells(10, .Columns.Count).End(xlToLeft))
            Set rngF = shtIF.Cells.Find(rngC.Value)
            If Not rngF Is Nothing Then
                shtIF.Cells(18, rngF.Column).Resize(shtIF.UsedRange.Rows.Count - 9).Copy shtNew.Columns(rngC.Column - 4)
            End If
        Next rngC
    End With
    
    shtNew.UsedRange.EntireColumn.AutoFit

It seems like its' made to adjust for adding/removing columns. Do U want code to insert columns or re-arrange them? When sheet settings are done this should be where the input file structure is created. Maybe a bit more info. Dave

I want the code that insert 2 columns (COI, WorkPackage) beside the chosen columns from users, not sure if this is possible.These 2 columns are mandatory. You can change the code you mentioned if needed.

 

[NdNoviceHlp]

What does your sheet settings do? Does the user enter columns in "A"? How are they put in the output order? What is the location of the output order? I'm guessing ideally the COI and workpackage columns will be added to "A" then placed in the output order. Then U want to make the columns correspond to the output order? Dave

 

[haitran]

The Sheet Settings is for users enter columns they want to save and put them in any order. Yes they will enter the columns they want to save to column "A". Like in the picture, user wants to save column "Procedure", "Comment...", "A time [min]", "Test Name". All the columns users enter to column "A" will be added to the ComboBox located above Output order (row 10). Users will choose a cell in row 10 and then choose a column from ComboBox, the column will be added to that cell. This is just in case there are many columns and users want to change the columns order around so they don't have to retype the columns name everytime they want to change the order. Here is the code of Sheet Settings so you can understand what I said.

Private Sub ComboBox1_Change()
    ActiveCell.Value = Me.ComboBox1.Value
End Sub
Public Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim lastColumnName, amountColumns, headerRowNumber, lastcolumn, row As Integer
    Dim strColumnName As String
    Dim blnCheckColumn As Boolean
    Dim i As Long, j As Long
    lastColumnName = Sheets("Settings").Cells(Rows.Count, 1).End(xlUp).row
    headerRowNumber = Sheets("Settings").Cells(1, 5).Value
    amountColumns = Sheets("Settings").Cells(2, 5).Value
    blnCheckColumn = False
    'Only checks if the columns exist
    For i = 2 To lastColumnName
        strColumnName = Sheets("Settings").Cells(i, 1).Value
        blnCheckColumn = False
        For j = 1 To amountColumns
            If strColumnName = Sheets("Inputfile").Cells(headerRowNumber, j).Value Then blnCheckColumn = True
        Next
    
        If blnCheckColumn = False Then
             MsgBox "Column '" & strColumnName & "' does not exist!"
            Exit Sub
        End If
    Next
    
    Me.ComboBox1.ListFillRange = ("A2:A" & Range("A" & Rows.Count).End(xlUp).row)
End Sub

And yes I want column COI and WorkPackage will be added to column A. Then I can put them in output order.
Thank you very much for the help.

 

[NdNoviceHlp]

Adding columns, is this a one time thing or do U want the flexibility to add/remove columns at any time? Will there be data in the sheet when U re-arrange or add/remove columns? So far I think when U add a new column by adding it to the list in "A" you could simply put the new header in the first empty column to the left. The columns will be later arranged anyways. I think U should fill the list in "A" based on the requested output order then use "A" to order the columns. Am I close? Dave

 

[haitran]

Sorry I kinda lost in what you said there. User will enter columns they want to save to column "A" (these columns they choose from Sheet "InputFile"), they can also remove the columns if they change their mind. Then they order the columns with "Output order". When they run the macro, a new Workbook will be created with the saved columns in the correct order. The column COI and WorkPackage are not from Sheet "InputFile", they are extra columns I want to add, these 2 columns will always be in the list of column "A" (users can't remove them) and can be put in the "Output order". Hope you can understand the situation :D .

 

[NdNoviceHlp]

Why not just manually add them to begin with? Add them to combobox then code to prevent their removal. The "A" list is generated by the output order which is produced by the combobox selections. Have I got that right? What is the purpose of "A" if it is a duplicate of the output order... is it just to review what output order has been created? Dave