Excel File To a Text File

[pbt]

I'm having problems converting to a text file from Excel.

The below code worked in 2003, but now I have put it in 2007 with just a change in the Range to work with.

What I'm getting is a Text file with, for lack of a better term, hyroglifics (sp).
Not the numbers that I should get like I got in 2003.

Private Sub CommandButtonExportPAG_Click()
    Application.ScreenUpdating = False
    On Error GoTo errTrap
    '//Hide the rows with 0 values
    LastRow = Range("S65536").End(xlUp).Row
     For r = LastRow To 263 Step -1
        If (Range("S" & r).Value = 0) Then
            Rows(r).Delete
        End If
    Next r
Application.Wait (Now + TimeValue("0:00:01"))
    Range("K264", Range("S264").End(xlDown).Offset(0, -1)).Copy
        Sheets.Add
        ActiveSheet.Name = "TEMP"
    Selection.PasteSpecial Paste:=xlValues
    Application.CutCopyMode = False
    Application.Range("A1").Select

    Sheets("TEMP").Copy
    Application.DisplayAlerts = False

    ActiveWorkbook.SaveAs Filename:= _
        "E:\Documents and Settings\michele_p.NSP\Desktop\Job Setup.txt", FileFormat _
        :=xlText, CreateBackup:=False
    
    ActiveWindow.Close
    ActiveWindow.SelectedSheets.Delete
        Sheets("PAG").Range("K264", Range("S264").End(xlDown)).ClearContents
        Range("A1").Select
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    SplashForm.Show
    Exit Sub
errTrap:
    Application.Dialogs(xlDialogSaveAs).Show ("Job Setup.txt")
    ActiveWindow.Close
    ActiveWindow.SelectedSheets.Delete
        Sheets("PAG").Range("K264", Range("S264").End(xlDown)).ClearContents
         Range("A1").Select
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    SplashForm.Show
         
End Sub

Anybody have an idea?

Thanks
Harry

 

[pbt]

Yes that is correct!

(I wish I knew how you put that little square in the post)

 

[jindon]

And you will have that one for the first time, but never after 2nd time for the same data.
Is it correct?

 

[pbt]

Lets see if this helps:

Excel Workbook
	K	L	M	N	O	P	Q	R	S
263	CONTRACT	LOT/BLK	TYPE	CODE	DESCRIPTION	LABOR	BURDEN	MATERIAL	TOTALS
264	5788	55	22	MT10	Lath Mat'l Total	*	*	1279.25	1279.25
265	*	55	*	MT60	Synth Mat'l Subtotal	*	*	*	*
266	*	55	*	MT20	Nails & Staples	*	*	86.46	86.46
267	*	55	*	MT30	Masking Mat'l Total	*	*	22.79	22.79
268	*	55	*	MT40	Brown Mat'l Total	*	*	396.63	396.63
269	*	55	*	MT50	Finish Mat'l Total	*	*	91.58	91.58
PAG

This is the range from Excel that will be converted to Text file. With code provided, It gives the square above "5788". This is just one record.

With two "records", (transported with the Compile() below the first):

Excel Workbook
	K	L	M	N	O	P	Q	R	S
291	3420	66	33	MT10	Lath Mat'l Total	*	*	738.60	738.60
292	*	66	*	MT60	Synth Mat'l Subtotal	*	*	*	*
293	*	66	*	MT20	Nails & Staples	*	*	52.38	52.38
294	*	66	*	MT30	Masking Mat'l Total	*	*	13.09	13.09
295	*	66	*	MT40	Brown Mat'l Total	*	*	240.30	240.30
296	*	66	*	MT50	Finish Mat'l Total	*	*	55.49	55.49
PAG

Converting one or two "records" to Text file gives the little box character as the first row in the Text file. Have not tried three or four records yet.

EDITThere is other info down to row 291, just posted a short version

EDIT 2 disregard the * cells are blank

 

[jindon]

Harry

What I guess is that the value in K264 which is 5788 includes hidden code.
Is it number or text? (seems text)
If text, can you convert it to a number?
1) select K264
2) go to [Data] - [TextToColumn] and click on [Finish]

 

[pbt]

Harry

What I guess is that the value in K264 which is 5788 includes hidden code.
Is it number or text? (seems text)
If text, can you convert it to a number?
1) select K264
2) go to [Data] - [TextToColumn] and click on [Finish]

It is formated as General.

Converted to to Number, same thing, little box at row one of Text file

1) & 2) Did that, same results as above.

What I guess is that the value in K264 which is 5788 includes hidden code.

I don't see how since K264 is populated with VALUE from I5. (which does have a formula)

But next record which also populates col K with (new) value from I5 does not have a problem. ???

 

[jindon]

Hummm
Can you just try

Open fn For Output As #1
    Print #1, Mid$(txt, Len(vbCrLf) + 1)
Close #1

 

[pbt]

That got rid of the rectangle from row one of the Text file.

Info transported to Text file starts at row 2.

Don't know if this will work when I inport the file into the program that takes the Text file. (don't remember if program reads row 1)

Don't have access to it right now.
_____________________________________________________


Your code seems to being doing what I want. Even though I still don't understand the "RegExp" and the "WScript.Shell" part of it, not to mention the "Open fn For Output As #1", but I'm still lost on one question:

Why does my code that was posted in the begining of this thread works with 2003 but does not work with 2007??

I have assumptions as to why, but I'm pobably stabbing in the dark. So better I don't say at this time.

Harry

 

[jindon]

Ok
If it works, the code should do the job.

Sub test1()
Dim fn As String, txt As String
fn = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\Job Setup.txt"
With Sheets("PAG")  '<- change chere
    For Each r In .Range("s264", .Range("s" & Rows.Count).End(xlUp))
        If r.Value <> 0 Then txt = txt & vbCrLf & Join(Evaluate("transpose(transpose(" & _
         r.Offset(, -8).Resize(, 8).Address & "))"), vbTab)
    Next
End With
Open fn For Output As #1
    Print #1, Mid$(txt, Len(vbCrLf) + 1)
Close #1
End Sub

I haven't experienced such bug □ before, but now I need to more careful.

I don't know the syntax for saving Excel file as VBTextFormat for 2007, but seems slightly different maybe?

Anyway, Text file is a Text File, you don't need Excel to save as .txt.
You know what I mean ?

 

[pbt]

Thank you for your help on this even though it took awhile.

It works fine now as far as converting to a Text file.

I have another file to convert to a Text File, I'm hoping that I can do it on my own. Especially since I have your lead on this.

Anyway, Text file is a Text File, you don't need Excel to save as .txt.
You know what I mean ?

Sorry, I do not know what you mean.

Harry

 

[jindon]

To generate a Text fille, simply

1) Open text editor (NotePad, MSWord)
2) wright something
3) saveas .txt

So you don't need to convert Excel file to .txt file when particularly in your case.

What my code is doing is, picking up the line (row) to be in a text file under colS <> 0, and concatenate all the values in the row with Tab.
So that you don't need the process to Add extra worksheet/Copy the rows/SaveAs....