View Full Version : Various items - exporting to various .txt files where duplicates

07-20-2014, 03:31 PM
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
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


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
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
LineStr = LineStr & rng.Cells(CountR, CountC)
End If
LineStr = LineStr & IIf(CountC < NumC, Delim, "")
LineStr = LineStr & IIf(Trailing, Delim, "")
ts.WriteLine LineStr

' release memory from object variables
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
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
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
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
.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,


07-20-2014, 07:03 PM
As an approach, why don't you create 1 very hidden worksheet per text file, and then save each sheet as .txt?


07-21-2014, 01:32 AM
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.

07-21-2014, 04:06 PM
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

07-22-2014, 01:06 AM

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.


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.



07-22-2014, 04:25 PM
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


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?)