Extracting data...

[George]

I am working on a project that will read data from a *.csv file and
process it in a new workbook. The new workbook will have the macro to
handle this process. I have a couple questions:

I am assuming a file must be open in order to extract data.

Does the *.csv file have to be activated after the file is open in order
to begin processing data? How can this be done without losing focus
from the new workbook?

Thanks,
GeoK

 

[Gary''s Student]

Right from the Recorder:

Sub Macro1()
ChDir "C:\"
Workbooks.Open Filename:="C:\x.csv"
Windows("Book1").Activate
Range("I7").Select
End Sub

 

[ker_01]

Another version, from one of my workbooks- this copies the entire CSV into a
target worksheet in your 'active' workbook.

Sub Grab_Current_Raw_Data()
Dim wb As Workbook
Dim ws As Worksheet
Dim fn As String
fn = "\\drive\path\folder\filename.csv"
Set ws = Sheet1 'Worksheets("Data")
Set wb = Workbooks.Open(fn)
wb.Worksheets(1).Cells.Copy
ws.Activate
ws.Range("A1").PasteSpecial
wb.Close SaveChanges:=False
End Sub

 

[Gary''s Student]

Very good - nice and crisp.

 

[Patrick Molloy]

you don't need to open it in the excel application...you could read the data
in as a textstream - means using the Microsoft Scripting Runtime.
You'll need to open a connection to the file though.

in the dev environment, set a reference the Microsoft Scripting Runtime
add this code to a standard module:

Option Explicit
Const sFILE As String = "C:\temp\test.csv"
Sub GetData()
Dim textst As TextStream
Dim data As Variant
Dim text As String
Dim rowindex As Long
With New FileSystemObject
Set textst = .OpenTextFile(sFILE, ForReading, False)
Do Until textst.AtEndOfStream
rowindex = rowindex + 1
text = textst.ReadLine
data = Array(Split(text, ","))
With Cells(rowindex, 1)
.Value = text
.TextToColumns Cells(rowindex, 1)
End With
Loop
textst.Close
Set textst = Nothing
End With
End Sub

 

[George]

Very good - nice and crisp.

Thank you for the information. Very helpful!