Text file: How to Create, Use, and later Delete 2

[jimoo]

Hi, I'm new to this forum so this has probably been ask before. I didn't see in the FAQ section.

I need to create a small text file in Excel, the application will use the text file for another process, then I need to delete it.



Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[SBendBuckeye]

Let Excel write the code for you. Tools,Macro,Record New Macro. Open your file, do a Save As to a text file and then close the Excel file. Stop the recorder and look at your code.

Alt+F11 will get you into the VBA editor.

After you have closed the file then Kill(FileNameAndPath) will delete it.

Good Luck!

 

[Bowers74]

Hello Jim,

Welcome to Tek-Tips!

I am glad to see that you looked into the FAQ section first before poting your question. You could have also done a Keyword search (it might have helped you also).

The following code will export all of the data within a selection of cells into a TXT file named "C:\Textfile.txt". It should show you the basics on exporting data from Excel spreadsheets into a text file:

Sub ExportSelectionToTXT()
    Dim Filename As String
    Dim NumRows As Long, NumCols As Integer
    Dim r As Long, c As Integer
    Dim Data
    Dim ExpRng As Range
    Set ExpRng = Selection
    NumCols = ExpRng.Columns.Count
    NumRows = ExpRng.Rows.Count
    Filename = "c:\textfile.txt"
    Open Filename For Output As #1
        For r = 1 To NumRows
            For c = 1 To NumCols
                Data = ExpRng.Cells(r, c).Value
                If IsNumeric(Data) Then Data = Val(Data)
                If IsEmpty(ExpRng.Cells(r, c)) Then Data = ""
                If c <> NumCols Then
                    Write #1, Data;
                Else
                    Write #1, Data
                End If
            Next c
        Next r
    Close #1
End Sub

This should get you started!

I hope this helps!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[jimoo]

Thanks Sbend & Bower.

Sbend. It's not that simple. The file does not exist yet. I want to create it, use it in a web-query (get external data) and then delete it after the query is completed.

Bower. I'd rather do this seemlessly, if possible, without sending temporary data to a cell and then copying it to a text file. If I must, I must, but I'd rather not. Also, the output of the data had "" around it and I need it stripped off.

Here is a sample of a text query file I need to create, use, and delete. It has 9 lines of text including one blank line.

Starting Below Here
WEB
1

http://moneycentral.msn.com/investor/external/excel/rates.asp

Selection=EntirePage
Formatting=All
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
Ending Above Here


Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[Kryzsoccer]

'This creates the file
'If you are printing variables remove the quotes
Open "C:\YourTextFileName.txt" for output as #1
print #1, "WEB"
print #1, "1"
print #1, "

http://moneycentral.msn.com/investor/external/excel/rates.asp"

print #1, ""
print #1, "Selection=EntirePage"
print #1, "Formatting=All"
print #1, "PreFormattedTextToColumns=True"
print #1, "ConsecutiveDelimitersAsOne=True"
print #1, "SingleBlockTextImport=False"
close #1

'Now it is made and you can use it and do whatever you want with it
Open "C:\YourTextFileName.txt" for output as #2
input #2, myvar
close #2


'This deletes it
kill("C:\YourTextFileName.txt&quot


Let me know how it goes!!

 

[Kryzsoccer]

OOPS!!

THIS WAS WRONG IN MY PREVIOUS POST:
'Now it is made and you can use it and do whatever you want with it
Open "C:\YourTextFileName.txt" for output as #2
input #2, myvar
close #2


IT SHOULD BE:
'Now it is made and you can use it and do whatever you want with it
Open "C:\YourTextFileName.txt" for input as #2
input #2, myvar
close #2

 

[jimoo]

Thanks KryzSoccor. Worked like a charm and worth a star.

However, now I have one more question, which is probably easier.

I can run the create file time and time again with no problem, but the delete command gives me a "file not found" error if it does not exist.

How can I trap to see if it exists prior to issuing the kill command?

Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[Bowers74]

To prvent that error from happening, change the code to this:

On Error Resume Next
kill("C:\YourTextFileName.txt")
On Error GoTo 0

I hope this helps!


Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Bowers74]

If you want to run a Web Query, then why not just record the creation of a web query using the Macro Recorder. The following code was recorded and I edited it a bit jsut to shorten it up a bit, I left all of the properties from the Web Query Creation there so that you can play around with them:

Sub WebQuery()
Cells.ClearContents
With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;[URL unfurl="true"]http://moneycentral.msn.com/investor/external/excel/rates.asp",[/URL] _
    Destination:=Range("A1"))
    .Name = "rates_1"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = False
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlAllTables
    .WebFormatting = xlWebFormattingRTF
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .Refresh BackgroundQuery:=False
End With
Range([B5], [C5].End(xlDown)).NumberFormat = "#,##0.00"
Cells.EntireColumn.AutoFit
End Sub

This is actually your example web query (without using TextFiles), so go ahead and run it to see if that is what you want! ;-)

I hope this helps!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[jimoo]

Thanks Bowers74.

I want to create the query file (text) manually for 2 reasons.

1. Based on the user's selection I will create the query file. In other words, maybe they want the data formatted or maybe unformatted, etc.

This way I don't have a bunch of files for every possibility.

2. In some instances I don't want them to know what URL I am hitting and the file contains the URL.

Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com