Solved: ExecuteExcel4Macro

[mdmackillop]

Can anyone identify the bug in this?

Option Explicit
Sub ProcessFiles()
Dim MyPath As String, MyName As String, SheetName As String
Dim z As Long, Order As String
z = 1
MyPath = "C:\csvfiles\"
MyName = Dir("C:\csvfiles\*.csv")
Do While MyName <> ""
Order = Split(MyName, ".")(0)
SheetName = Left(MyName, (Len(MyName) - 4))
Sheets(1).Cells(z, 2) = GetData(MyPath, MyName, SheetName, "$CP$20")
Sheets(1).Cells(z, 1) = Order
z = z + 1
MyName = Dir
Loop
End Sub
Private Function GetData(Path, File, Sheet, Address)
Dim Data$
Data = "'" & Path & "[" & File & "]" & Sheet & "'!" & _
Range(Address).Range("A1").Address(, , xlR1C1)
Debug.Print Data
GetData = ExecuteExcel4Macro(Data)
Debug.Print GetData
End Function

[Bob Phillips]

Malcolm,

The problem seems to be that it is a csv file. If yopu open the csv file, save it as an xls file, and then try that with the xls file, it works fine. Just doesn't work with csv files.

[mdmackillop]

Pity, I've got 1200 files to process. I can do my task by opening each. It's just a bit slow that way.
Regards
Malcolm

[Bob Phillips]

Pity, I've got 1200 files to process. I can do my task by opening each. It's just a bit slow that way.
Regards
Malcolm

It's odd. You can reference a csv file directly, and I thought the Excel4 macro just tapped that facility.

[Bob Phillips]

BTW, could you not use ADO to read it? Might be quicker.

[stanl]

BTW, could you not use ADO to read it?

and CopyfromRecordset(). I do this to import .csv files on county statistics (North Carolina - 100 Counties) into a single Workbook with 100 named tabs. Stan

[mdmackillop]

I just need the one "cell" from each workbook , CP20, to be written into a column of data. I can cope with the processing time :wine: , so this is more out of interest than essential.

[stanl]

If you are iterating through 1200 workbooks to concatenate a single cell value from each, why not build a variable that is delimited with vbcrlf instead of "," - copy to clipboard, then .Paste(.range("a1")). Or if the data already exists as a .csv, load to a var, perform global replace then put it through the clipboard - or have I not read your intent correctly. Stan

[mdmackillop]

Thanks for the suggestion Stan.
I've dumped Excel4 for the following. I've still some data problems, but this code is getting the basic info for me.

Option Explicit
Dim fs, f, s, ts
Sub ProcessFiles()
Dim MyPath As String, MyName As String
Dim z As Long
z = 1
[D65536].End(xlUp).Offset(1) = Timer
Application.ScreenUpdating = False
Set fs = CreateObject("Scripting.FileSystemObject")
MyPath = "C:\csvfiles\"
MyName = Dir("C:\csvfiles\*.csv")
Do While MyName <> ""
Sheets(1).Cells(z, 1) = Split(MyName, ".")(0)
Sheets(1).Cells(z, 2) = GetCSVData(MyPath & MyName, 21, 93)
z = z + 1
MyName = Dir
Loop
Set fs = Nothing
Application.ScreenUpdating = True
[D65536].End(xlUp).Offset(1) = Timer
End Sub

Function GetCSVData(strFilePath, Rw As Long, Col As Long) As String
Set f = fs.GetFile(strFilePath)
Set ts = f.OpenAsTextStream(1, -2) 'open mode "For Reading"
s = ts.Readall
s = Split(s, vbCr)(Rw)
On Error Resume Next
GetCSVData = Split(s, ",")(Col)
ts.Close
Set f = Nothing
Set ts = Nothing
End Function

[Bob Phillips]

Thanks for the suggestion Stan.
I've dumped Excel4 for the following. I've still some data problems, but this code is getting the basic info for me.

Option Explicit
Sub ProcessFiles()
Dim MyPath As String, MyName As String
Dim z As Long
z = 1
Application.ScreenUpdating = False
MyPath = "C:\csvfiles\"
MyName = Dir("C:\csvfiles\*.csv")
Do While MyName <> ""
Sheets(1).Cells(z, 1) = Split(MyName, ".")(0)
Sheets(1).Cells(z, 2) = GetCSVData(MyPath & MyName, 21, 93)
z = z + 1
MyName = Dir
Loop
Application.ScreenUpdating = True
End Sub

Function GetCSVData(strFilePath, Rw As Long, Col As Long) As String
Dim fs, f, s, ts

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(strFilePath)
Set ts = f.OpenAsTextStream(1, -2) 'open mode "For Reading"
s = ts.Readall
s = Split(s, vbCr)(Rw)
On Error Resume Next
GetCSVData = Split(s, ",")(Col)
ts.Close
Set fs = Nothing
Set f = Nothing
Set ts = Nothing
End Function



Create the FSO object in the first procedure, don't create it for every retrieve, big overhead.

[mdmackillop]

Thanks Bob,
Will do!