Export certain cells to txt file

[Lhuffst]

I have a spreadsheet that has columns A - I. I need to export columns a, b, c, g and I (all rows). The export file has to be a text file.
I haven't worked with excel and was hoping someone had some code that would accomplish this.
Thanks
Lhuffst

 

[SkipVought]

Hi,

Is this a ONE TIME thing, or something you'll do regularly?

ONE TIME: Select the unwanted columns
OTHERWISE: Run a query on another sheet, just including the columns you want. faq68-5829

SaveAs any Text File format.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Lhuffst]

This will be a bi-weekly run. Thanks for the help

 

[Bong]

I think the simplest thing to do is to use the SaveAs method. Since this only applies to workbooks or worksheets, first you would need to create a (temporary) new sheet:
thisworkbook.Sheets.Add After:=[red]<your last sheet>[/red]
Now that added sheet will be sheets(sheets.count)

Now you want to create a range of the data you want. From your description, this comprises a rectangular range from a1 to gN, where N is some "last row", and another range from i1 to iN. I don't know how your data is configured but if it's even remotely "nice", usedrange should work:
lrow=sheet1.UsedRange.Rows.Count
set r1=sheet1.Range(cells(1,1),cells(lrow,7))
set r1=application.Union(r1,range(cells(1,9),cells(lrow,9)))

Now you can copy that range to the new sheet:
r1.copy(sheets(sheets.count).[a1])

Now you can save that sheet:
sheets(sheets.count).SaveAs Filename:=<filename>, FileFormat := xlTextWindows

Now delete the new sheet
sheets(sheets.Count).delete
This will display a dialog box that the user will have to respond to.


_________________
Bob Rashkin

 

[Bong]

My last post assumed that the data of interest was on sheet1. If not, you would substitute the correct sheet designation.

_________________
Bob Rashkin

 

[Lhuffst]

Bob That worked great! Thanks much

 

[Lhuffst]

Oops. When I went back and tested again, I run into problems with the sheets.count values.

What I've noticed is that even though I have been deleting the sheets each time, when it runs the first step of adding a sheet, it increments from the last one.

I expected it to be Sheet1 (the original sheet) and sheet2 - the sheet where the data is copied. When you run it the 2nd, 3rd ...times it shows a sheet4 or 5 ...

When you step through the code, the value for sheets.count is 2 but the sheet name doesn't match.

I think if I rename sheet2 to FHOUT then I could change the lines
r1.copy(sheets(sheets.count).[a1] to
r1.copy(sheets("FHOUT").[a1]

and

sheets(sheets.count).saveas Filename.. to
sheets("FHOUT").saveas

and

Sheets(sheets.count).delete to
sheets("FHOUT").DELETE

Am I understanding this correctly?

 

[SkipVought]

sheet2 is not necessarily Sheets(2). The INDEX order is the TAB order and has nothing to do with the sheet object code name.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Bong]

That's why I suggest (obliquely in my post, I admit) that you do sheets.count to get the index. When you add a sheet, it will always have a greater index number than the previously (before the sheet was added) last sheet.

_________________
Bob Rashkin

 

[Lhuffst]

don't know what I had wrong last week but it works fine now. Last question on this. My current output looks like

209NW08 7776255 001 04/02/08 Vendor Painted
209NW08 7776255 003 04/04/08 Vendor Not Painted
209NW08 7776255 004 04/06/08 Vendor Repainted
209NW08 7776255 005 04/08/08 Vendor New Hydrant


But the user needs all of the spaces removed so it looks like


209NW08777625500104/02/08VendorPainted
209NW08777625500304/04/08VendorNot Painted
209NW08777625500404/06/08VendorRepainted
209NW08777625500504/08/08VendorNew Hydrant

How would I remove the space between each column and export the results?
Thanks
Lhuffst

 

[Bong]

I don't know what they all do, but there are several different types of text file for "SaveAs":

xlTextMac
xlTextMSDOS
xlTextPrinter
xlTextWindows
xlUnicodeText

I guess I'd play with those first and see if any produces the desired result. If not, I think you'll have to go a whole different route and open a file, formulate a string, output the string,...repeat. That would be a very different approach:

for each rw in sheets(sheets.count).usedrange.rows
   strOut = ""
   for each c in rw.cells
     strOut = strOut & c.value
   next
   <<file output>>
next

for instance.

_________________
Bob Rashkin

 

[SkipVought]

Format cells as Courier 8

AutoFit columns

SaveAs using text file type .prn

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Lhuffst]

Thanks everyone. It works fine now. The code that I used is:

ThisWorkbook.Sheets.Add after:=Sheet1
lrow = Sheet1.UsedRange.Rows.Count
Set r1 = Sheet1.Range(Cells(2, 1), Cells(lrow, 3))
Set r1 = Application.Union(r1, Range(Cells(2, 7), Cells(lrow, 9)))
Set r2 = Sheet1.Range(Cells(2, "K"), Cells(lrow, "K"))

'Concatanate the fields
Dim counter
For counter = 2 To lrow
Cells(counter, "K").Value = _
Cells(counter, 1).Value & Cells(counter, 2).Value & Cells(counter, 3).Value & _
Cells(counter, 7).Value & Cells(counter, 8).Value & Cells(counter, 9).Value
Next counter

'copy to new sheet
'r1.Copy (Sheets(Sheets.Count).[a1])
r2.Copy (Sheets(Sheets.Count).[a1])

'save the new sheet
Sheets(Sheets.Count).SaveAs Filename:="D:\My Data\FireHydrant Database\MainframeSide\testit.txt", FileFormat:=xlTextWindows

'delete the temporary sheet
Sheets(Sheets.Count).Delete

'close the text file
Close
'clear out the cells so it will be empty at the start
Sheet1.Range(Cells(1, "K"), Cells(lrow, "K")).ClearContents