Refere to cell representing a path to a file
 

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

Refere to cell representing a path to a file
 

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

Refere to cell representing a path to a file
 

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 th
code.

can you also place few comments for the function *ReadFirstLine*

--
mariuston
-----------------------------------------------------------------------
mariustony's Profile: http://www.excelforum.com/member.php...fo&userid=2591
View this thread: http://www.excelforum.com/showthread.php?threadid=47288

Refere to cell representing a path to a file
 

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

Refere to cell representing a path to a file
 

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

Refere to cell representing a path to a file
 

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

Refere to cell representing a path to a file
 

I tried using instr() but this returns only a number, the position wher
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 uniqu
identifier, the date.

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

The only thing in common that the *txt* and *csv* files have is thi
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*, wher
*are the paths for the csv files*.

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

-In that moment, copy the columns C,D,E from the csv and insert it i
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 m
a solution, because I don't know how to write it.

Note: the date has the same position in every txt file, and sam
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

--
mariuston
-----------------------------------------------------------------------
mariustony's Profile: http://www.excelforum.com/member.php...fo&userid=2591
View this thread: http://www.excelforum.com/showthread.php?threadid=47288

Refere to cell representing a path to a file
 

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