Refere to cell representing a path to a file

[mariustony]

I got many cells from A1 to A500... every of it containing a path to a txt file (D:\Test\test1.txt ... and so on). Does somebody know how to refere in my macro at these cells, so I can open the files they are refering at ?
I want to do this in order to verify if they have a keyword inside, so I want to open them and see if there is that word present.

Basically I thought to make a for statement which refers to A1:A500 range, and inside it to Open the files from these cells, verify if the first word is "Complete", and if not, to copy the whole path for the file in another Workbook or Sheet.

The problem is I don't know if it's possible, and how to write it in VB code.
Any idea or help is high appreciated.
Thanks.

[Dave Peterson]

How about this....

You insert a new column B in your worksheet. You cycle through the cells in
column A and put the results in that new column B.

Then you can apply data|filter|autofilter to that new column B and filter to
show the results you want.

You could copy the visible rows to another worksheet--or just use the filter.

Option Explicit
Sub testme()

Dim myCell As Range
Dim myRng As Range
Dim TstStr As String

With Worksheets("sheet1")
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In myRng.Cells
myCell.Offset(0, 1).Value = ReadFirstLine(myCell.Value)
Next myCell

End Sub
Function ReadFirstLine(myInputFileName As String) As String

Dim FileNum As Long
Dim myLine As String
Dim myStr As String

myStr = "complete"

FileNum = FreeFile
Close FileNum
On Error Resume Next
Open myInputFileName For Input As FileNum
If Err.Number <> 0 Then
Err.Clear
ReadFirstLine = "Bad file name"
Else
Line Input #FileNum, myLine
Close FileNum
If LCase(Left(myLine, Len(myStr))) = LCase(myStr) Then
ReadFirstLine = "is Complete"
Else
ReadFirstLine = "not complete"
End If
End If

End Function

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

mariustony wrote:
>
> I got many cells from A1 to A500... every of it containing a path to a
> txt file (D:\Test\test1.txt ... and so on). Does somebody know how to
> refere in my macro at these cells, so I can open the files they are
> refering at ?
> I want to do this in order to verify if they have a keyword inside, so
> I want to open them and see if there is that word present.
>
> Basically I thought to make a for statement which refers to A1:A500
> range, and inside it to Open the files from these cells, verify if the
> first word is "Complete", and if not, to copy the whole path for the
> file in another Workbook or Sheet.
>
> The problem is I don't know if it's possible, and how to write it in VB
> code.
> Any idea or help is high appreciated.
> Thanks.
>
> --
> mariustony
> ------------------------------------------------------------------------
> mariustony's Profile: http://www.excelforum.com/member.php...o&userid=25913
> View this thread: http://www.excelforum.com/showthread...hreadid=472889

--

Dave Peterson

[mariustony]

Hi,

Thanks for the given code.
The thing is that I cannot run it because at line

With Worksheets("Sheet1")

occurs the error "Subscript out of range (Error 9)"

and I don't know why is that. so, further more I couldn't go with the code.

can you also place few comments for the function ReadFirstLine ?

[Dave Peterson]

My data was on a worksheet named "Sheet1".

What's the name of the worksheet that contains your data.

Change this to match:
With Worksheets("Sheet1")

Function ReadFirstLine(myInputFileName As String) As String

'declare some variables

Dim FileNum As Long
Dim myLine As String
Dim myStr As String

'what to look for
myStr = "complete"

'assign a number to FileNum
FileNum = FreeFile

'just in case that file was open, close it
Close FileNum

'what happens if your filename is spelled wrong
'just ignore the error and test later
On Error Resume Next

'open the file with the name that was passed to the subroutine.
Open myInputFileName For Input As FileNum

'everything ok???
If Err.Number <> 0 Then
'if no, then give a nice warning and get the heck out.
Err.Clear
ReadFirstLine = "Bad file name"
Else
'read the first record
Line Input #FileNum, myLine

'and we're done reading, so close that file.
Close FileNum

'look for Complete, COMPLETE, CoMpLeTe in the first 8 characters
If LCase(Left(myLine, Len(myStr))) = LCase(myStr) Then
'found it? return a nice message
ReadFirstLine = "is Complete"
Else
'nope? return that error message
ReadFirstLine = "not complete"
End If
End If

End Function

Tom Ogilvy just posted this and it might help you:

http://web.archive.org/web/200404050...eio/fileio.asp

File Access with Visual Basic® for Applications
(one line in your browser)

or
http://tinyurl.com/dghhf

mariustony wrote:
>
> Hi,
>
> Thanks for the given code.
> The thing is that I cannot run it because at line
>
> With Worksheets("Sheet1")
>
> occurs the error "Subscript out of range (Error 9)"
>
> and I don't know why is that. so, further more I couldn't go with the
> code.
>
> can you also place few comments for the function *ReadFirstLine* ?
>
> --
> mariustony
> ------------------------------------------------------------------------
> mariustony's Profile: http://www.excelforum.com/member.php...o&userid=25913
> View this thread: http://www.excelforum.com/showthread...hreadid=472889

--

Dave Peterson

[mariustony]

Hi,

The problem with not accepting the Worksheets is that I was doing a search of the files using a Shell command which was generating me an excel file, because I was specifying the extension. But it creates it only with one Sheet and does not accept references to Worksheets. Only Range("A1:An").

For this I have to copy the search results into an excel workbook and then run the macro.
I just wanted to thank you for it and to tell you this thing which was interesting for me.

I created another Sheet in my workbook with results for the *.csv files which are containing the data missing from the txt files.

Now the next step is to complete the NotCompleted txt files.

First I have to compare a string from their names to find a match:

textfile_2004_05_17.txt with complete_2004_05_17_file.csv

the match is "2004_05_17"

this is happening with every txt file, it has a match with a csv file.

Do you know how to make this compare??

Txt files are in Sheet2 and csv files are in Sheet3, the same Workbook.

Thanks again for any help provided.

[Dave Peterson]

I don't quite understand what you want to do.

If you're comparing a string in VBA, you could use instr().

If you're comparing using a worksheet function, you could use:
=isnumber(search(yourstring,yourcell))



mariustony wrote:
>
> Hi,
>
> The problem with not accepting the Worksheets is that I was doing a
> search of the files using a Shell command which was generating me an
> excel file, because I was specifying the extension. But it creates it
> only with one Sheet and does not accept references to Worksheets. Only
> Range("A1:An").
>
> For this I have to copy the search results into an excel workbook and
> then run the macro.
> I just wanted to thank you for it and to tell you this thing which was
> interesting for me.
>
> I created another Sheet in my workbook with results for the *.csv files
> which are containing the data missing from the txt files.
>
> Now the next step is to complete the NotCompleted txt files.
>
> First I have to compare a string from their names to find a match:
>
> TEXTFILE_2004_05_17.TXT[/B] WITH *COMPLETE_2004_05_17_FILE.CSV
>
> the match is "[B]2004_05_17*"
>
> this is happening with every txt file, it has a match with a csv file.
>
> Do you know how to make this compare??
>
> Txt files are in Sheet2 and csv files are in Sheet3, the same
> Workbook.
>
> Thanks again for any help provided.
>
> --
> mariustony
> ------------------------------------------------------------------------
> mariustony's Profile: http://www.excelforum.com/member.php...o&userid=25913
> View this thread: http://www.excelforum.com/showthread...hreadid=472889

--

Dave Peterson

[mariustony]

I tried using instr() but this returns only a number, the position where my string is found.

I would like to see if I find the string:

"2004_03_17" - this being specific for one txt file and/or
"2005_10_22" - this is specific to another txt file

....and so on............ every file has in it's name an unique identifier, the date.

so, in the list where I have listed the paths for 200 txt files (in column A), I take file by file, and look for it's correspondent in Sheet2, where are the csv files located.

The only thing in common that the txt and csv files have is this date.
One txt file can have only one csv file associated.

In one For statement as I imagine should be the following:

-extract this date from the path of each txt file in Sheet1 Column A and search for the string ".....date...." in Sheet2 Column A, where are the paths for the csv files.

-When found the correspondent string, it means that the txt file found it's csv file.

-In that moment, copy the columns C,D,E from the csv and insert it in the columns A,B,C of the corresponding txt file, then Save it.

Then Next .....the same operation for the next cell.

I don't know how clear I was this time, but I hope now you can give me a solution, because I don't know how to write it.

Note: the date has the same position in every txt file, and same position in every csv file, between them the position is different.
-----example: textfile_2004_03_17.txt
source_2004_03_17_file.csv

thank you for any help in this direction.

[Dave Peterson]

I'm confused about all the asterisks in your file names. They can't be used in
a windows filename.

You can use
if instr(...) = 0 then
'it wasn't found.
else
'it was found at that position.
end if

Ahhh. Those asterisks are bold in excelforum!

I don't quite see what you're doing.

But you could open each of the files and search for dates.


mariustony wrote:
>
> I tried using instr() but this returns only a number, the position where
> my string is found.
>
> I would like to see if I find the string:
>
> "*2004_03_17*" - this being specific for one txt file and/or
> "*2005_10_22*" - this is specific to another txt file
>
> ...and so on............ every file has in it's name an unique
> identifier, the date.
>
> so, in the list where I have listed the paths for 200 *txt* files (in
> column A), I take file by file, and look for it's correspondent in
> Sheet2, where are the *csv* files located.
>
> The only thing in common that the *txt* and *csv* files have is this
> date.
> One txt file can have only one csv file associated.
>
> In one For statement as I imagine should be the following:
>
> -extract this date from the *path of each txt file in Sheet1 Column A*
> and search for the string ".....date...." in *Sheet2 Column A*, where
> *are the paths for the csv files*.
>
> -When found the correspondent string, it means that the txt file found
> it's csv file.
>
> -In that moment, copy the columns C,D,E from the csv and insert it in
> the columns A,B,C of the corresponding txt file, then Save it.
>
> Then Next .....the same operation for the next cell.
>
> I don't know how clear I was this time, but I hope now you can give me
> a solution, because I don't know how to write it.
>
> Note: the date has the same position in every txt file, and same
> position in every csv file, between them the position is different.
> -----example: textfile_*2004_03_17*.txt
> source_*2004_03_17*_file.csv
>
> thank you for any help in this direction.
>
> --
> mariustony
> ------------------------------------------------------------------------
> mariustony's Profile: http://www.excelforum.com/member.php...o&userid=25913
> View this thread: http://www.excelforum.com/showthread...hreadid=472889

--

Dave Peterson