PDA

View Full Version : €50 REWARD for urgent help



jeremyp
11-06-2012, 03:21 AM
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
11-06-2012, 03:28 AM
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

jeremyp
11-06-2012, 03:50 AM
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
11-06-2012, 04:03 AM
That could be the case (remove the =):


C:\Users\Sammy\Desktop\Sample\Output\0NTEwMzMxAAAAAAAAAAAAAFP6Bg5meUHL8Mk.c sv

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

You can do a simple check


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


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

jeremyp
11-06-2012, 08:14 AM
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
11-06-2012, 08:28 AM
use:



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