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]

Thank you that makes since.

What I'm still lost on is the (my) code that works in 2003 doese not work with 2007.

You had to go to "Creat Object" etc. etc. to make it work for 2007.

Why would a my code written for 2003 not work with 2007 ??

Harry

 

[jindon]

CreateObject("WScript.Shell").SpecialFolders("Desktop")

It is not because of the Version, it is just calling SpecialFolders function from WSH (Windows Scripting Host).
It returns DeskTop path for any PC (when multi user, logged in desktop)
You can also use FileSystemObject.

 

[pbt]

One little bit of help:

This is what I currently have, (with my attempt to solve, which does not work)

Private Sub CommandButtonExportPAG_Click()
    On Error GoTo errTrap
    Dim fn As String, txt As String
   ' Dim MyCell As Variant  '\\added
   ' Set MyCell = Range("J1").Value  '\\added
    Application.ScreenUpdating = False
fn = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\Job Setup & MyCell.txt" '\\added
With Sheets("PAG")
    For Each r In .Range("s93", .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
    Sheets("PAG").Range("K93", Range("S93").End(xlDown)).ClearContents
    Range("A1").Select
    Application.ScreenUpdating = True
    SplashForm.Show
    Exit Sub
errTrap:
    Sheets("PAG").Range("K93", Range("S93").End(xlDown)).ClearContents
    Range("A1").Select
    Application.ScreenUpdating = True
    SplashForm.Show
End Sub

I need to run this more then once before exporting the Text files into my other program.

Each time it runs, it overwrites the file on the Desktop.

Is there way to make it rename the Text file each time it is run?
Job Setup(2), Job Setup(3), etc. is okay.

Or if I can grab the Value from say J1 to be the name of the Text file. J1 will change each time it runs.

Thanks
Harry

 

[jindon]

How about

fn = CreateObject("WScript.Shell").SpecialFolders("Desktop") & _
       "\Job Setup & MyCell.txt"

to

fn = CreateObject("WScript.Shell").SpecialFolders("Desktop") & _
       "\Job Setup(" & Format$(Now,"mmddyy-hh:mm:ss") & ").txt"

 

[pbt]

Thanks jindon

I reads all the way to Open fn For Output As #1 the goes to errTrap:

Harry

 

[jindon]

May be ":"
Can you change

"mmddyy-hh:mm:ss"

to

"mmddyy-hhmmss"

 

[pbt]

Thanks jindon, again

"mmddyy-hhmmss"

worked, grouped the time as one number

"mmddyy-hh-mm-ss"

Also worked with the hyphen between the hour, min, sec.

Harry