Consulting

Results 1 to 6 of 6

Thread: Various items - exporting to various .txt files where duplicates

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1

    Unhappy Various items - exporting to various .txt files where duplicates

    Hello all,

    I'm new to the wonderful world of VBA and have been getting stuck in for a week now. I am slightly out of my depth and would appreciate some help.

    I'm currently running Excel 2010 and trying to do the following:
    1.) Export a selection (fixed width columns J to CP and dynamic range of rows) to .txt files on a row by row basis (part solved, see below)
    2.) Move duplicates based on a value in column Q to different .txt files, so that there is only one unique row based on the column Q value in each .txt file.

    For part 1, I have found a great add-in that exports my data perfectly, however it requires manual selection of a range via a form, however, I need the tab to be very hidden, thus need this automated to not allow the end user access/view it. As such, I wish to remove the form altogether, and have the form take the values of the range I need to export from a defined range. My current module which defines my range to be exported succesfully:
    Sub ExportUKBL()
        'use End(xlToLeft) to determine last column with data in row (row number x)
        Dim lastColumn As Integer
        Worksheets("UKBL").Activate
        lastColumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column 'row number here
        
        'use End(xlUp) to determine Last Row with Data, in one column (column x)
        Dim lastRow As Long
        lastRow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row 'column letter here
        
        Range(Cells(lastRow, 10), Cells(6, lastColumn)).Select
        
        'STEP TO BE INSERTED TO SAVE TO .TXT
        
        MsgBox "UK BL" & vbCrLf & "-----------------------------------" & vbCrLf & "Exported succesfully.", , "Data Export"
    End Sub
    Module 1 - For Userform
    Option Explicit
    
    Sub MakeFile()
        
        Dim rng As Range
        Dim NumR As Long
        Dim NumC As Long
        Dim CountR As Long
        Dim CountC As Long
        Dim Delim As String
        Dim Qual As String
        Dim Leading As Boolean
        Dim Trailing As Boolean
        Dim TheFile As String
        Dim fso As Object
        Dim ts As Object
        Dim LineStr As String
        
        UserForm1.Show 'TO REMOVE
        
        ' get variable setting from UserForm
        With UserForm1 ' TO REMOVE WITH
            Set rng = Range(.reRange)
            NumR = rng.Rows.Count
            NumC = rng.Columns.Count
            Delim = ","
            Qual = ""
            Leading = False
            Trailing = False
            TheFile = .tbCreateFile 'TRY TO SET A FILENAME UP HERE SO IT DOESN'T REQUIRE FORM?
        End With
        Unload UserForm1
        
        ' create the text file
        Set fso = CreateObject("Scripting.FileSystemObject")
        Set ts = fso.CreateTextFile(TheFile, True)
        
        ' loop through range to build text file records
        For CountR = 1 To NumR
            LineStr = IIf(Leading, Delim, "")
            For CountC = 1 To NumC
                If Not IsNumeric(rng.Cells(CountR, CountC)) And Not IsDate(rng.Cells(CountR, CountC)) Then
                    LineStr = LineStr & Qual & rng.Cells(CountR, CountC) & Qual
                Else
                    LineStr = LineStr & rng.Cells(CountR, CountC)
                End If
                LineStr = LineStr & IIf(CountC < NumC, Delim, "")
            Next
            LineStr = LineStr & IIf(Trailing, Delim, "")
            ts.WriteLine LineStr
        Next
        
        ' release memory from object variables
        ts.Close
        Set ts = Nothing
        Set fso = Nothing
           
        MsgBox "UKBL" & vbCrLf & "-----------------------------------------------------------------" & vbCrLf & "Exported succesfully " & TheFile, , "Data Export"
        
    End Sub
    My UserForm
    Option Explicit
    Private Sub cbWorkbook_Change()
        With Me
                Workbooks(.cbWorkbook.Value).Activate 'WHAT FORM SAYS WORKBOOK IS - WANT TO MAKE THAT CURRENT WORKBOOK ALWAYS
        End With
    End Sub
    Private Sub cbWorksheet_Change()
        With Me
                .reRange = Worksheets("UKBL").Select
                .reRange.Enabled = True 'WHAT FORM SAYS WORKSHEET IS
                .LabelRng.Enabled = True
            End With
    End Sub
    Private Sub cmdChange_Click()
    .tbCreateFile = Application.GetSaveAsFilename(.tbCreateFile, "Text Files (*.txt), *.txt", , _
              "Save Text File to...") 'WHAT FORM SAYS SAVEFILE IS
    End Sub
    Private Sub cmdGo_Click()
        Dim rng As Range
        With Me
            On Error Resume Next
            Set rng = Range(.reRange) 'WHAT FORM SAYS RANGE IT (WANT TO REPLACE)
            On Error GoTo 0
            ThisWorkbook.Worksheets("Sheet1").Range("cbWorkbook") = .cbWorkbook
            ThisWorkbook.Worksheets("Sheet1").Range("cbWorksheet") = .cbWorksheet
            ThisWorkbook.Worksheets("Sheet1").Range("reRange") = .reRange
            ThisWorkbook.Worksheets("Sheet1").Range("tbCreateFile") = .tbCreateFile
            ThisWorkbook.Save
            .Hide
        End With
    End Sub
    Private Sub tbCreateFile_Change()
    End Sub
    Private Sub UserForm_Initialize()
        Dim wb As Workbook
        With Me
            For Each wb In Workbooks
                .cbWorkbook.AddItem wb.Name
            Next
            .cbWorksheet.Enabled = False
            .LabelWs.Enabled = False
            .reRange.Enabled = False
            .LabelRng.Enabled = False
            On Error Resume Next
                .cbWorkbook = ThisWorkbook.Worksheets("Sheet1").Range("cbWorkbook")
                cbWorksheet_Change 'calls workbook change?
                .cbWorksheet = ThisWorkbook.Worksheets("Sheet1").Range("cbWorksheet")
                .reRange = ThisWorkbook.Worksheets("Sheet1").Range("reRange")
            On Error GoTo 0
            .tbCreateFile = ThisWorkbook.Worksheets("Sheet1").Range("tbCreateFile")
            End With
    End Sub
    I attach a copy of my spreadsheet layout for your reference.

    I would be grateful if anyone can help me out with the above. I have inched closer to reaching a solution on part 1, but have no clue as to the best way to approach part 2. I hope I have been sufficiently clear above.

    Thanks all in anticipation,

    Oscar
    Attached Files Attached Files

  2. #2
    VBAX Mentor
    Joined
    Aug 2012
    Posts
    367
    Location
    As an approach, why don't you create 1 very hidden worksheet per text file, and then save each sheet as .txt?

    Tim
    Remember: it is the second mouse that gets the cheese.....

  3. #3
    The data on the left hand side (columns A to H) is imported via another macro, and is dumped on this tab, thus I can't create different worksheets. I assume this can be done via importing the data, then somehow sorting it into different tabs, then exporting each tab?

    Furthermore, I need to force the 1st row (row 6) to be included as a header line in each exported .txt file.

  4. #4
    VBAX Mentor
    Joined
    Aug 2012
    Posts
    367
    Location
    Create 1 tab (worksheet) per export file.
    copy and paste the values that you wish to export, and arange them in the correct layout on the new tab (worksheet)
    use sort and filter commands untill you are happy
    delete rows if you need to
    when you have a sheet (tab) with the required layout, export it to txt file

    if you do this with the macro recorder turned on, you will have a first draft of the required code.

    rewrite the code to use defined worksheets (instead of activesheet etc)
    define the search and delete criteria, then automate this with a loop (you might need two nested loops)

    ---------

    The best way to learn this is to start with the macro recorder, and then hack the code.
    a quick google search on this will give you a good idea of its strengths and weaknesses.

    as a quick starting suggestion, learn to use range objects early. you will also most likely need to use arrays to do this - and you would benefit from reading up on how to copy from an array to a range in one go. this would let you build one array (page) for each export, then write it to a sheet and export it.

    -------------

    it will be much easier to help you with specific code commands, as the workbook still leaves a few questions on what you wish to achieve, and a great many questions on what search criteria can be used to subset your data

    happy hunting
    Tim
    Remember: it is the second mouse that gets the cheese.....

  5. #5
    Tim,

    Thank you for the tips. I was researching the issue last night and have indeed settled more or less on your solution.

    My current spreadsheet imports data from a separate excel report into my calculations spreadsheet on the left hand side. This will then format all the data for export correctly on the right hand side.

    I will then have various very hidden tabs and use a countif statement to remove my duplicates via a filter, and copy the header row into row 1 of these sheets before exporting all spreadsheets if there any values in them.

    My issues is thus that I am comfortable with working with the fixed column range and the dynamic ranges, using offset and resize etc. My issue is that I require a specific output format to the .txt file, and the above add-in code is the only solution that produces the right result, without leading or trailing delimiters, and comma separated values. I am not really at a level where I can successfully manipulate the code above to remove the form (see below). I need to substitute the form so that it automatically selects my a range via a defined range (fixed columns, dynamic rows via End(xlUp) etc), as the end user will not be able to select the data as I require the sheets to be very hidden.

    Form.jpg

    I've manipulated the code to the extent that I can, but am not familiar with With statements and how to remove the form entirely from the code without breaking it.

    As always, any help would be gratefully received.

    Thanks,

    Oscar

  6. #6
    VBAX Mentor
    Joined
    Aug 2012
    Posts
    367
    Location
    to understand with:

    consider the following:
    set mySheet = activesheet
    
    with mysheet
      .name = "test"
      .range("A1").value = 1
    end with
    is the same as:
    activesheet.name = "test"
    activesheet.range("A1").vlaue = 1
    except that it calls the mysheet object once, instead of repeatedly so is quicker as well as visually tidier


    for the remainder, you might wish to consider either a delimited text file where the delimiting character is either "" or " "
    or use the string concatenation, so:
    myString = string1 & " " & string2
    etc,

    this will build a very long string that resides only in the first column.
    (There is enough that I don't follow about your existing code that I am avoiding critiquing this - can anyone else show more wisdom?)

    Tim
    Remember: it is the second mouse that gets the cheese.....

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •