Reading text file in macro

[Helpful person]

I wish to read data from a text file from within a Visual Basic for
Applications macro that is attached to a command button in my
spreadsheet. From the user's point of view it should look like this.

1. User clicks the button
2. In some manner the user inputs the file name. Ideally this should
be using the windows API file open call, however, I don't think this
is available through VBA
3. The macro opens the file and reads data.
4. macro closes file.

Any help will be greatly appreciated.

 

[Pete_UK]

This should do it - it takes data from cell C1 of the text file and
puts it in A1. The text file I tested it on had 3 fields of data.

Sub File_open()
'
' File_open Macro
' Macro recorded 14/07/2008 by Pete Ashurst
'
Dim file_name As String
file_name = Application.GetOpenFilename()
Workbooks.OpenText Filename:=file_name, _
Origin:=xlWindows, StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1))
cell_read = Cells(1, 3).Value
ActiveWorkbook.Close
Cells(1, 1).Value = cell_read
End Sub

Hope this helps.

Pete

 

[Helpful person]

This should do it - it takes data from cell C1 of the text file and
puts it in A1. The text file I tested it on had 3 fields of data.

Sub File_open()
'
' File_open Macro
' Macro recorded 14/07/2008 by Pete Ashurst
'
    Dim file_name As String
    file_name = Application.GetOpenFilename()
    Workbooks.OpenText Filename:=file_name, _
        Origin:=xlWindows, StartRow:=1, _
        DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, _
        Tab:=True, Semicolon:=False, _
        Comma:=True, Space:=False, Other:=False, _
        FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1))
    cell_read = Cells(1, 3).Value
    ActiveWorkbook.Close
    Cells(1, 1).Value = cell_read
End Sub

Hope this helps.

Pete





- Show quoted text -

Thanks very much. I'll read this carefully and may come back with
further questions,. However at first glance it seems to do the job.

 

[Pete_UK]

You're welcome, but note that there is no error checking in the macro.

Pete

 

[Helpful person]

This should do it - it takes data from cell C1 of the text file and
puts it in A1. The text file I tested it on had 3 fields of data.

Sub File_open()
'
' File_open Macro
' Macro recorded 14/07/2008 by Pete Ashurst
'
    Dim file_name As String
    file_name = Application.GetOpenFilename()
    Workbooks.OpenText Filename:=file_name, _
        Origin:=xlWindows, StartRow:=1, _
        DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, _
        Tab:=True, Semicolon:=False, _
        Comma:=True, Space:=False, Other:=False, _
        FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1))
    cell_read = Cells(1, 3).Value
    ActiveWorkbook.Close
    Cells(1, 1).Value = cell_read
End Sub

Hope this helps.

Pete





- Show quoted text -

Actually this does not seem to do what I need.

If I understand the OpenText method correctly it opens a text file as
a new worksheet. I do not want to open a new worksheet, just read
data (in a VBA macro) from a plane text file into variable in that
macro.

Help please!

 

[Pete_UK]

I recorded that example based on a text file which had 3 columns of
data in it (as stated), and the item which is in cell C1 (once
imported) is written to the variable cell_read and then the workbook
is closed. If you only have one item of data in your text file, then
change the macro to this:

Sub File_open()
'
' File_open Macro
' Macro recorded 14/07/2008 by Pete Ashurst
'
Dim file_name As String
file_name = Application.GetOpenFilename()
Workbooks.OpenText Filename:=file_name, _
Origin:=xlWindows, StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, _
FieldInfo:=Array(Array(1, 1))
cell_read = Cells(1, 1).Value
ActiveWorkbook.Close
'
' do what you want with cell_read ...
'
End Sub

Perhaps you can modify this to suit what you want to do.

Hope this helps.

Pete

 

[Helpful person]

I recorded that example based on a text file which had 3 columns of
data in it (as stated), and the item which is in cell C1 (once
imported) is written to the variable cell_read and then the workbook
is closed. If you only have one item of data in your text file, then
change the macro to this:

Sub File_open()
'
' File_open Macro
' Macro recorded 14/07/2008 by Pete Ashurst
'
    Dim file_name As String
    file_name = Application.GetOpenFilename()
    Workbooks.OpenText Filename:=file_name, _
        Origin:=xlWindows, StartRow:=1, _
        DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, _
        Tab:=True, Semicolon:=False, _
        Comma:=True, Space:=False, Other:=False, _
        FieldInfo:=Array(Array(1, 1))
    cell_read = Cells(1, 1).Value
    ActiveWorkbook.Close
'
'  do what you want with cell_read ...
'
End Sub

Perhaps you can modify this to suit what you want to do.

Hope this helps.

Pete




- Show quoted text -

Thanks very mush for your response. However I have managed to solve
my problem thus:


Private Sub CommandButton1_Click()
Dim FileSaveName As Variant
Dim File_Name_String As String
Dim Text_Stream As Object
Dim fs As Object
Dim prompt As String
Dim Dummy_String As String
'Open dialog box and get file name to save from user
FileSaveName = Application.GetSaveAsFilename("")
'Check to see if user cancelled action
If FileSaveName <> False Then
'Convert the name of the file to a string
File_Name_String = CStr(FileSaveName)
'Create object for saving file
Set fs = CreateObject("Scripting.FileSystemObject")
'Ask user if they want to overwrite file
If fs.FileExists(File_Name_String) Then
Dummy_String = "File " & File_Name_String & " already exists.
Do you want to replace the existing file?"
prompt = MsgBox(Dummy_String, vbOKCancel)
If prompt = 2 Then Exit Sub
End If
'Associate text stream with file and allow to overwrite previous
data
Set Text_Stream = fs.CreateTextFile(File_Name_String, True)
'Output to file
Text_Stream.write "my test, not Hello World"
'Close file
Text_Stream.Close
End If
End Sub

 

[Pete_UK]

Glad to see you got something that works for you - thanks for feeding
back.

However, this code seems to write data to the text file, whereas I
thought you wanted to read data and allocate it to a variable.

Pete

 

[Helpful person]

Glad to see you got something that works for you - thanks for feeding
back.

However, this code seems to write data to the text file, whereas I
thought you wanted to read data and allocate it to a variable.

Pete






- Show quoted text -

I need both. However, I can use the same methods to do both.

I'm modeling an enginewring system and want the user to be able to
save and read data from an ASCII file. This way I can update the
spreadsheet and they can still read in their data the same way without
worrying about transfering their old data.

 

[Helpful person]

With more research I eventually came up rith this code which does a
better job.


Private Sub CommandButton1_Click()
Dim FileSaveName As Variant
Dim File_Name_String As String
Dim Text_Stream As Object
Dim fs As Object
Dim prompt As String
Dim Dummy_String As String
Dim File_Exists As Boolean
Dim File_Number As Integer
'Open dialog box and get file name to save from user
FileSaveName = Application.GetSaveAsFilename("")
'Check to see if user cancelled action
If FileSaveName <> False Then
'Convert the name of the file to a string
File_Name_String = CStr(FileSaveName) 'Create object for saving
file
'Check tosee if the file exists
File_Exists = (Dir(File_Name_String) <> "")
If File_Exists Then
'Ask user if they want to overwrite file
Dummy_String = "File " & File_Name_String & " already exists.
Do you want to replace the existing file?"
prompt = MsgBox(Dummy_String, vbOKCancel)
If prompt = 2 Then Exit Sub
End If
File_Number = FreeFile()
Open File_Name_String For Output As File_Number
Write #File_Number, "my test, not Hello World"
Write #File_Number, "my test again, not Hello World"
Close #File_Number
End If
End Sub