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:

Code:

---

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

Code:

---

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

Code:

---

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

As an approach, why don't you create 1 very hidden worksheet per text file, and then save each sheet as .txt?

Tim

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.

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

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.

Attachment 11997

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

to understand with:

consider the following:

Code:

---

set mySheet = activesheet

with mysheet
  .name = "test"
  .range("A1").value = 1
end with

---

is the same as:

Code:

---

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:

Code:

---

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