€50 REWARD for urgent help

[jeremyp]

Dear VBA Express community,

I will reward whoever manages to help me out with €50 transferred in whatever means of payment you prefer (paypal, wire transfer etc).

I have a list of CSV files in a folder, and an excel file listing all of these files.

What I am trying to do is to create a new column to the right of the search paths for the CSV files that gets me $A$1 from all of these cells.

I have attached example to make it more clear. Please note that all the CSV contain only 1 data point (i.e all cells other than $A$1 are empty). Also note that the list I have provided is only a sample - It would be great if the VBA macro was as "general" as possible, so something in the lines of:

1. Select all file folder references in columna A
2. Select folder in which the CSV fiels to be searched are placed
3. Let the magic happen

Kind regards,
Jeremy

[snb]

[VBA]Sub M_snb00()
With CreateObject("scripting.filesystemobject")
For Each cl In Columns(1).SpecialCells(2).Offset(1).SpecialCells(2)
cl.Offset(, 1) = Split(.opentextfile(cl.Value).readall, vbCrLf)(0)
Next
End With
End Sub[/VBA]

[jeremyp]

Dear SNB,

Thank you for that. However I get runtime error '76': path not found.
Is my path in an incorrect format?

C:\Users\Sammy\Desktop\Sample\Output\0NTEwMzMxAAAAAAAAAAAAAFP6Bg5meUHL8Mk=. csv

Kind regards,
Jeremy

[snb]

That could be the case (remove the =):

C:\Users\Sammy\Desktop\Sample\Output\0NTEwMzMxAAAAAAAAAAAAAFP6Bg5meUHL8Mk.csv

From here I can't see the structure of your directories, nor the filenames.

You can do a simple check

[vba]
msgbox Dir("C:\Users\Sammy\Desktop\Sample\Output\*.csv")
[/vba]

If nothing is being displayed in the messagebox it means the path is invalid, or the folder doesn't contain any csv file.

[jeremyp]

I got it to work, but not 100%

Is there a way to make it skip a file if it does not find it in the folder path? Now, if a file does not exist, it generates an error and pauses operation

Kind regards,
Eyass

[snb]

use:

[VBA]
Sub M_snb00()
With CreateObject("scripting.filesystemobject")
For Each cl In Columns(1).SpecialCells(2).Offset(1).SpecialCells(2)
if dir(cl.value)<>"" then cl.Offset(, 1) = Split(.opentextfile(cl.Value).readall, vbCrLf)(0)
Next
End With
End Sub
[/VBA]