Excel File To a Text File

P

pbt

Well-known Member
Joined
Oct 18, 2005
Messages
1,613
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.

Code: 
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
 
Sort by date Sort by votes
Harry
Just try
Code: 
Sub test()
Dim fn As String, txt As String
fn = "E:\Documents and Settings\michele_p.NSP\Desktop\Job Setup.txt"
With Sheets("YourSheetnameHere")  '<- 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(" & _
                  r.Offset(,-8).Resize(,9).Address & ")"),vbTab)
    Next
End With
Open fn For Output As #1
    Print #1, Mid$(txt,2)
Close #1
End Sub
 
Upvote 0
Thank you jindon for the reply.

One question before I try this code.

If "michelle" is not the user that is going to do this conversion, will your code default to the SaveAs dialog box if another user is running this?

I don't quite follow your code as yet. (if it will do the above)

Harry
 
Upvote 0
OK
change to
Rich (BB code): 
fn = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\Job Setup.txt"
 
Upvote 0
Code: 
Sub test()
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(" & _
                  r.Offset(, -8).Resize(, 9).Address & ")"), vbTab)
    Next
End With
Open fn For Output As #1
    Print #1, Mid$(txt, 2)
Close #1
End Sub

Error #5 Invalid procedure call or argument, at this part:
Code: 
txt = txt & vbCrLf & Join(Evaluate("transpose(" & _
                  r.Offset(, -8).Resize(, 9).Address & ")"), vbTab)

Harry
 
Upvote 0
OOps, need another transpose...
Rich (BB code): 
txt = txt & vbCrLf & Join(Evaluate("transpose(transpose(" & _
                  r.Offset(, -8).Resize(, 9).Address & "))"), vbTab)
 
Upvote 0
Don't have time to try that last post,(have to do something for the wife)

BUT, I was thinking that maybe should save to, whoever is working on the file, "Desktop" instead of one individual.

Be back in 30 minutes(sorry)

Harry
 
Upvote 0
Okay, I have tried working this code (jindon's) but still come up with one problem.

Col K gives an extra character at the front when transfered to the Text file. Everything else seems to work just fine.

Using this:
Code: 
Sub test()
Dim fn As String, txt As String
fn = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\Job Setup.txt"
With Sheets("PAG")
    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, 2)
Close #1
Sheets("PAG").Range("K264:S1000").ClearContents
End Sub

My range starting in K264 is populated with this code:
Code: 
Private Sub CommandButtonCompile_Click()
    Dim MyCell As Range
    On Error GoTo errTrap
        If Range("I4") = "" Or Range("I9") = "" Or Range("I8") = "" Then
            MsgBox Prompt:="You must enter a Contract Amount, an Address and a Lot-Blk number", _
            Buttons:=vbCritical, Title:="                                       ERROR"
        Exit Sub
    End If
    Application.ScreenUpdating = False
        Range("A11:H37").Copy
    Set MyRange = Range("L65536").End(xlUp).Offset(1, 0)
        MyRange.PasteSpecial Paste:=xlValues
        Application.CutCopyMode = False
   Set MyCell = Range("L65536").End(xlUp).Offset(-26, 0)
        MyCell.Offset(0, -1) = Range("I5").Value
Call LaborDispatch
    Range("J11:J262,I8:I9").ClearContents
    Range("I8").Select
    Exit Sub
errTrap:
    Application.ScreenUpdating = True
End Sub

I have checked col K for extra spaces, etc., none

Also if if run the Compile_Click procedure a second time (which is common) to convert two (or more) "records" into the Text file.

The second "record" col K does not have the extra character in the Text file.

Lost
Harry
 
Upvote 0
Harry

It is apparently the data.

1) Does copied data include formula reault ?
2) What is actual "extra charcter" ?
 
Upvote 0
jindon said:
Harry

It is apparently the data.

1) Does copied data include formula reault ?
2) What is actual "extra charcter" ?
Click to expand...

1) Yes I5 has =I4-D8 which goes to K264 with Compile procedure

2) Explanation not good but, it looks like a small rectangle standing on the short side just before the value that came from "K".

Only happens on first record that was "Compiled"
 
Last edited by a moderator:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Vba to create a new excel file and convert all cell formulas to text
Replies
1
Views
121
Candyman8019
C
C
  • Question Question
Finding the problem with the code that gives the message "The picture is too large and will be truncated" ...
2
Replies
14
Views
313
NdNoviceHlp
NdNoviceHlp
L
  • Question Question
Splitting worksheets in Excel with Senstivity Labels following master file using VBA
Replies
5
Views
401
NdNoviceHlp
NdNoviceHlp
B
  • Question Question
VBA to delete attachment created on desktop
Replies
4
Views
93
bh24524
B
Ramadan
  • Solved
Override the last saved sheet as paf file
Replies
5
Views
184
Ramadan
Ramadan

Forum statistics

Threads
1,226,795
Messages
6,193,046
Members
453,772
Latest member
aastupin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top