PDA

View Full Version : Import a plain file of data into tab



magicalstone
10-24-2008, 02:33 AM
Hi,

Is it possible to use VBA to import the data from a file into one column (varying amounts of rows) of a worksheet?

Basically I have an exported file of data, 1 column in width with varying amounts of rows (this changes week by week as the file is exported) and I want the user to be able to specify the location of this file (by browsing for it as you would when you open a file normally if possible) and then have VBA take the contents and put it into a worksheet called PRODUCTFILE in my workbook.


Is this possible? If so do you have any tips or advice on how to achieve it?

Thanks

mdmackillop
10-24-2008, 02:53 AM
Record a macro importing the file into a blank worksheet, then copy and paste to PRODUCTFILE. You'll need to determine the last used cell (check the KB for examples) and paste below this.
If you need more assistance, let us know.

magicalstone
10-24-2008, 03:05 AM
This seems to work if the file has the same name each time, but it will have a different name each time and will be saved in different locations. Is there a way to allow the user to specify the file to import?

GTO
10-24-2008, 04:47 AM
Greetings magicalstone,

Several ways to return desired file, this but one. Not tested well, but appears no errrors. Loops, case large txt file (vs ReadAll)

Hope this helps,

Mark

Sub TextFile_RetrieveCol()
'//Microsoft Scripting Runtime / scrun.dll - or equiv req'd... or change to Dim as //
'// Object //
Dim _
FSO As FileSystemObject, _
fso_TextFile As File, _
fso_TxtStream As TextStream, _
rngText As Range, _
lLine As Long, _
strFilter As String, _
strFileName As String
'// Show only text files//
strFilter = "Text Files (*.txt) (*.txt),"".txt"
'//Return the textfile's fullname or "False" if cancelled//
strFileName = Application.GetOpenFilename _
(FileFilter:=strFilter, _
FilterIndex:=5, _
Title:="Select a TextFile, Open or Cancel to Quit")
'// Set the file open path to wherever you have text file(s)//
ChDir ThisWorkbook.Path
If Not strFileName = "False" Then
'// Create FSO, get the file, open it as TextStream//
Set FSO = CreateObject("Scripting.FileSystemObject")
Set fso_TextFile = FSO.GetFile(strFileName)
Set fso_TxtStream = fso_TextFile.OpenAsTextStream
'// For when we hit the end of the text file//
On Error Resume Next
Do While Err.Number = 0
lLine = lLine + 1
'// Adjust sheet codename as neccessary, read line by line to cell//
Sheet1.Cells(lLine, 1).Value = fso_TxtStream.ReadLine
Loop
On Error GoTo 0
Err.Number = 0
End If
Set FSO = Nothing: Set fso_TextFile = Nothing: Set fso_TxtStream = Nothing
End Sub

GTO
10-24-2008, 05:29 AM
Tired... Can I have a "do over"?

Look around/about line 15. Change order (duhhhh).

Mark


'//OOPS - wrong order before...//
'// Set the file open path to wherever you have text file(s)//
ChDir ThisWorkbook.Path


'//Return the textfile's fullname or "False" if cancelled//
strFileName = Application.GetOpenFilename _
(FileFilter:=strFilter, _
FilterIndex:=5, _
Title:="Select a TextFile, Open or Cancel to Quit")