I am developing a macro which pulls a lot of information out of certai
.txt files and does calculations and such. The problem comes i
finding which files need to be parsed. There is no consistent fil
naming system. All we know is that it is in a specified folder an
that the 3rd line of the file will contain a string which the use
specifies.

Problem: This directory contains approx. 4000 files so we should no
attempt to open every file in the entire directory to parse.

I was thinking about trying to use the document properties to sif
through the files. For example, we do know that the file has to b
created on or after a certain date. Is this faster than opening th
entire file for output? (I assume it is)

Can anyone think of a way to find the required files faster?
Also, is there a fast way to get directly to a specific line number i
an input file (or specific line/col in a txt file?)

Thanks in advance

--
prepotenc
-----------------------------------------------------------------------
prepotency's Profile: http://www.excelforum.com/member.php...fo&userid=2415
View this thread: http://www.excelforum.com/showthread.php?threadid=38663

Can anyone just answer me the latter question?

Is there a way to parse a .txt file and go directly to a specified row
or column in the text file? (i.e. to get to row 3 in the text file
without iterating there)


--
prepotency
------------------------------------------------------------------------
prepotency's Profile: http://www.excelforum.com/member.php...o&userid=24155
View this thread: http://www.excelforum.com/showthread...hreadid=386630

I don't think you can "go directly" without having to count lines: in any
case it doesn't take long to count to 3...

How large are the files? Even if you checked them all it wouldn't take too
long (unless of course this is something you need to do every minute). For
a one-time job even a couple of minutes should be acceptable.


Tim.

--
Tim Williams
Palo Alto, CA


"prepotency" wrote
in message ...

Can anyone just answer me the latter question?

Is there a way to parse a .txt file and go directly to a specified row
or column in the text file? (i.e. to get to row 3 in the text file
without iterating there)


--
prepotency
------------------------------------------------------------------------
prepotency's Profile:
http://www.excelforum.com/member.php...o&userid=24155
View this thread: http://www.excelforum.com/showthread...hreadid=386630

As far as I know Tim's spot on, in that

(a) there's no way as far as I know - unless you start to get into
random access files;
and
(b) you will be amazed at how blindingly fast VBA can search through
thousands of files, parse each line, compare strings. It's amazing.

However, if you need to access specific points in a file... I could
understand why this might sound a good idea -- say you want to go to
line 9999, it *might* be quicker to read the file in one fell swoop
rather than bit by bit. (But surely - definitely - not -as Tim wrote -
if you need to just access data at the beginning of the file).

To do this maybe something like:

Sub ReadWholeFile()

Dim myFile As String
Dim myLines() As String
Dim hFile As Integer

hFile = FreeFile
Open "c:\test.txt" For Input As #hFile
myFile = Input(LOF(hFile), hFile)
Close #hFile

'If file has records of identical length (say 100)
'you could access line 999 thus:
myrecordlength = 100 + Len(vbCrLf)
Line999 = Mid$(myFile, myrecordlength * 999, myrecordlength)

'Alternatively, you could split everything up by line into an
'array
myLines = Split(myFile, vbCrLf)
'You can now address line 999 thus:
Line999 = myLines(999)

End Sub

I have no idea if this will be quicker or (massively) slower than
reading a large file piecemeal. I would be interested to know if you do
a performance test!

HTH,
G



Tim Williams wrote:
I don't think you can "go directly" without having to count lines: in any
case it doesn't take long to count to 3...

How large are the files? Even if you checked them all it wouldn't take too
long (unless of course this is something you need to do every minute). For
a one-time job even a couple of minutes should be acceptable.


Tim.

Please ignore my last post. I thought about this and decided to do a
test for curiosity's sake and was very surprised. You should definitely
go through line by line.

I created a file with 10,000 records of 100 char each. I then accessed a
specific line in the file, closed it and repeated accessing the line 100
times.

Test 1 uses Line Input to go through line by line.
Test 2 loads the whole file into memory and parses the string using MID.
Test 3 loads the whole file into memory and splits the string using SPLIT.

Even if you go through the file line by line and need to access the
penultimate record it is still almost twice as fast as loading the file
into memory and using MID and 2.5 times as fast as using SPLIT. I would
expect it to be faster in general but not if I'm accessing the last
record. Wow! It's probably the SPLIT and MID that take the time I guess.

**TEST BEGINS**
Accessing line 999
Reading 100 files
Test 1: 00:00 (nn:ss)
Test 2: 00:07
Test 3: 00:10
**TEST ENDS**

**TEST BEGINS**
Accessing line 9999
Reading 100 files
Test 1: 00:04
Test 2: 00:07
Test 3: 00:10
**TEST ENDS**

**TEST BEGINS**
Accessing line 99
Reading 1000 files
Test 1: 00:00
Test 2: 01:12
Test 3: 01:40
**TEST ENDS**


My test code in case you're interested:

Sub ReadWholeFileTest()

Dim myFile As String
Dim myLines() As String
Dim hFile As Integer
Dim LineToAccess As Long
Dim myPath As String
Dim NumberOfTimesToReadFile As Integer
Dim myStartTime As Date
Dim myLineCounter As Integer
Dim myLine As String
Dim myRecordLength As Long
Dim PositionOfRecord As Long

LineToAccess = 99
myPath = "c:\test.txt"
NumberOfTimesToReadFile = 100

Debug.Print "**TEST BEGINS**"
Debug.Print "Accessing line " & LineToAccess
Debug.Print "Reading " & NumberOfTimesToReadFile & " files"

'Test 1 - normal line by line
myStartTime = Now
For i = 1 To NumberOfTimesToReadFile
hFile = FreeFile
Open myPath For Input As #hFile
Do While Not EOF(hFile)
Line Input #hFile, myLine
myLineCounter = myLineCounter + 1
If myLineCounter = LineToAccess Then Exit Do
Loop
Close #hFile
myLineCounter = 0
Next i
Debug.Print "Test 1: " & Format(myStartTime - Now, "nn:ss")
Debug.Print myLine

'Test 2 - by parsing string using mid
myLine = ""
myRecordLength = 100 + Len(vbCrLf)
myStartTime = Now
For i = 1 To NumberOfTimesToReadFile
hFile = FreeFile
Open myPath For Input As #hFile
myFile = Input(LOF(hFile), hFile)
Close #hFile
PositionOfRecord = (myRecordLength * (LineToAccess - 1)) + 1
myLine = Mid$(myFile, PositionOfRecord, 100)
Next i
Debug.Print "Test 2: " & Format(myStartTime - Now, "nn:ss")
Debug.Print myLine


'Test 3 - by splitting string
myLine = ""
myRecordLength = 100 + Len(vbCrLf)
myStartTime = Now
For i = 1 To NumberOfTimesToReadFile
hFile = FreeFile
Open myPath For Input As #hFile
myFile = Input(LOF(hFile), hFile)
Close #hFile
myLines = Split(myFile, vbCrLf)
myLine = myLines(LineToAccess - 1)
Next i
Debug.Print "Test 3: " & Format(myStartTime - Now, "nn:ss")
Debug.Print myLine

Debug.Print "**TEST ENDS**"

End Sub




Gareth wrote:
As far as I know Tim's spot on, in that

(a) there's no way as far as I know - unless you start to get into
random access files;
and
(b) you will be amazed at how blindingly fast VBA can search through
thousands of files, parse each line, compare strings. It's amazing.

However, if you need to access specific points in a file... I could
understand why this might sound a good idea -- say you want to go to
line 9999, it *might* be quicker to read the file in one fell swoop
rather than bit by bit. (But surely - definitely - not -as Tim wrote -
if you need to just access data at the beginning of the file).

To do this maybe something like:

Sub ReadWholeFile()

Dim myFile As String
Dim myLines() As String
Dim hFile As Integer

hFile = FreeFile
Open "c:\test.txt" For Input As #hFile
myFile = Input(LOF(hFile), hFile)
Close #hFile

'If file has records of identical length (say 100)
'you could access line 999 thus:
myrecordlength = 100 + Len(vbCrLf)
Line999 = Mid$(myFile, myrecordlength * 999, myrecordlength)

'Alternatively, you could split everything up by line into an
'array
myLines = Split(myFile, vbCrLf)
'You can now address line 999 thus:
Line999 = myLines(999)

End Sub

I have no idea if this will be quicker or (massively) slower than
reading a large file piecemeal. I would be interested to know if you do
a performance test!

HTH,
G



Tim Williams wrote:

I don't think you can "go directly" without having to count lines: in
any
case it doesn't take long to count to 3...

How large are the files? Even if you checked them all it wouldn't
take too
long (unless of course this is something you need to do every
minute). For
a one-time job even a couple of minutes should be acceptable.


Tim.

Yes exactly. I guess my question in the OP was referring to the file
parsing method which uses the least memory (time taken to run). I had
considered using split or Mid but what we're really trying to get
around here is reading in the whole file. Furthermore with Split, it
takes so much time because it parses the whole file and writes its
contents into an array (the creation of the array is what takes so
long). With the Mid option, we are still forced to read on the whole
file. Here's a question: when you read in line by line does VBA still
open the whole file? I guess that's what I'm wondering. Because I
don't really want to open up the whole file. I just assumed that it
opened the whole file into ram and then started pulling the lines out
of it, which wouldn't necessarily save you any time.

Question: What's the fastest way to tab through every file in a
specified folder and how do you allow the user to browse and select a
folder (NOT a file)? Nagging question I was having.

G


--
prepotency
------------------------------------------------------------------------
prepotency's Profile: http://www.excelforum.com/member.php...o&userid=24155
View this thread: http://www.excelforum.com/showthread...hreadid=386630

Because I don't really want to open up the whole file<<

When you open a file, you 'open' the entire file. But it is not read.
When you then read the file VBA uses a pointer to determine whereabout
it is in the file, advancing that pointer as required.

The quickest way to do this depends on the records in the file. If th
records are a fixed length (you do not say whether they are but you di
say earlier '100 characters') then the quickest way is to use a rando
access file ("Open fname for Random Access Read Write as #Input_Fil
len = ) and use the Get/Put commands to read and write to the file.
You specify a record number to read or write and the system just goe
off and calculates the correct place for the file pointer and reads th
record, no need to read anything in between.

As for Parsing your files, I would be inclined to create a rando
access file with a single character field. Generate a hash code fro
the file name and use this as a file pointer to read this file. I
there's no record then insert a record and process the file. If ther
is a record you know that you have processed the file so you can ignor
it.

You could extent this idea to create an audit trail by including th
date/time and user in this 'audit file' which would then allow you t
find out when a particular file was processed.

Regards

Ric

--
Rich_
-----------------------------------------------------------------------
Rich_z's Profile: http://www.excelforum.com/member.php...fo&userid=2473
View this thread: http://www.excelforum.com/showthread.php?threadid=38663

Low level file io only reads the amount of the file that you tell it to
read.

If you have fixed length records, you can look at the random access method.

--
Regards,
Tom Ogilvy

"prepotency" wrote
in message ...

Yes exactly. I guess my question in the OP was referring to the file
parsing method which uses the least memory (time taken to run). I had
considered using split or Mid but what we're really trying to get
around here is reading in the whole file. Furthermore with Split, it
takes so much time because it parses the whole file and writes its
contents into an array (the creation of the array is what takes so
long). With the Mid option, we are still forced to read on the whole
file. Here's a question: when you read in line by line does VBA still
open the whole file? I guess that's what I'm wondering. Because I
don't really want to open up the whole file. I just assumed that it
opened the whole file into ram and then started pulling the lines out
of it, which wouldn't necessarily save you any time.

Question: What's the fastest way to tab through every file in a
specified folder and how do you allow the user to browse and select a
folder (NOT a file)? Nagging question I was having.

G


--
prepotency
------------------------------------------------------------------------
prepotency's Profile:
http://www.excelforum.com/member.php...o&userid=24155
View this thread: http://www.excelforum.com/showthread...hreadid=386630

Rich_z Wrote:
As for Parsing your files, I would be inclined to create a rando
access file with a single character field. Generate a hash code fro
the file name and use this as a file pointer to read this file.

I don't follow your terminology. You're saying to write a fil
composed of all the file names and then read in from that file to fin
all the files that the code will then parse? Please elaborate

--
prepotenc
-----------------------------------------------------------------------
prepotency's Profile: http://www.excelforum.com/member.php...fo&userid=2415
View this thread: http://www.excelforum.com/showthread.php?threadid=38663

I'm not sure what you mean "to tab".

To have a user select a folder, not a file, I use the below (which is
fantastic and clearly not my own work). This lets you specify a default
folder, have the user create a new folder - whatever you need.

Watch for wrapping - it's going to be bad. I couldn't find a decent
page of this to link to so I've pasted it below.

One module with this code:

Option Explicit

Dim CntrDialog As Boolean

Private Type BROWSEINFO
hOwner As Long 'Handle to the owner window for the
dialog box.

pidlRoot As Long 'Address of an ITEMIDLIST structure
specifying the location
'of the root folder from which to
browse. Only the specified
'folder and its subfolders appear
in the dialog box.
'This member can be NULL; in that
case, the namespace
'root (the desktop folder) is used.

pszDisplayName As String 'Address of a buffer to receive the
display name of the folder
'selected by the user. The size of
this buffer is assumed to
'be MAX_PATH bytes.

lpszTitle As String 'Address of a null-terminated
string that is displayed above
'the tree view control in the
dialog box. This string can be
'used to specify instructions to
the user.

ulFlags As Long 'Flags specifying the options
for the dialog box.
'See constants below

lpfn As Long 'Address of an
application-defined function that the dialog box calls
'when an event occurs. For more
information, see the
'BrowseCallbackProc function.
This member can be NULL.

lParam As Long 'Application-defined value that the
dialog box passes to the
'callback function (in pData), if
one is specified

iImage As Long 'Variable to receive the image
associated with the selected folder.
'The image is specified as an index
to the system image list.
End Type

Private Const WM_USER = &H400
Private Const MAX_PATH = 260

'ulFlag constants
Private Const BIF_RETURNONLYFSDIRS = &H1 'Only return file system
directories.
'If the user selects
folders that are not
'part of the file system,
the OK button is grayed.

Private Const BIF_DONTGOBELOWDOMAIN = &H2 'Do not include network
folders below the
'domain level in the tree
view control

Private Const BIF_STATUSTEXT = &H4 'Include a status
area in the dialog box.
'The callback
function can set the status text
'by sending messages
to the dialog box.

Private Const BIF_RETURNFSANCESTORS = &H8 'Only return file system
ancestors. If the user selects
'anything other than a
file system ancestor, the OK button is grayed

Private Const BIF_EDITBOX = &H10 'Version 4.71. The
browse dialog includes an edit control
'in which the user can
type the name of an item.

Private Const BIF_VALIDATE = &H20 'Version 4.71. If the user
types an invalid name into the
'edit box, the browse
dialog will call the application's
'BrowseCallbackProc with
the BFFM_VALIDATEFAILED
' message. This flag is
ignored if BIF_EDITBOX is not specified

Private Const BIF_NEWDIALOGSTYLE = &H40 'Version 5.0. New dialog
style with context menu and resizability

Private Const BIF_BROWSEINCLUDEURLS = &H80 'Version 5.0. Allow URLs to
be displayed or entered. Requires BIF_USENEWUI.

Private Const BIF_BROWSEFORCOMPUTER = &H1000 'Only return computers. If
the user selects anything
'other than a computer, the
OK button is grayed

Private Const BIF_BROWSEFORPRINTER = &H2000 'Only return printers. If
the user selects anything
'other than a printer,
the OK button is grayed.

Private Const BIF_BROWSEINCLUDEFILES = &H4000 'The browse dialog will
display files as well as folders

Private Const BIF_SHAREABLE = &H8000 'Version 5.0. Allow display of
remote shareable resources. Requires BIF_USENEWUI.

'Message from browser to callback function constants

Private Const BFFM_INITIALIZED = 1 'Indicates the browse dialog box
has finished initializing.
'The lParam parameter is NULL.

Private Const BFFM_SELCHANGED = 2 'Indicates the selection has
changed. The lParam parameter
'contains the address of the item
identifier list for the newly selected folder.

Private Const BFFM_VALIDATEFAILED = 3 'Version 4.71. Indicates the user
typed an invalid name into the edit
'box of the browse dialog. The
lParam parameter is the address of
'a character buffer that
contains the invalid name.
'An application can use this
message to inform the user that the
'name entered was not valid.
Return zero to allow the dialog to be
'dismissed or nonzero to keep
the dialog displayed.

' messages to browser from callback function
Private Const BFFM_SETSTATUSTEXTA = WM_USER + 100
Private Const BFFM_ENABLEOK = WM_USER + 101
Private Const BFFM_SETSELECTIONA = WM_USER + 102
Private Const BFFM_SETSELECTIONW = WM_USER + 103
Private Const BFFM_SETSTATUSTEXTW = WM_USER + 104

Private Const LMEM_FIXED = &H0
Private Const LMEM_ZEROINIT = &H40
Private Const LPTR = (LMEM_FIXED Or LMEM_ZEROINIT)

'Main Browse for directory function
Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long
'Gets path from pidl
Declare Function SHGetPathFromIDList Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As
String) As Long
'Used by callback function to communicate with the browser
Declare Function SendMessage Lib "user32" _
Alias "SendMessageA" (ByVal hwnd As Long, _
ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Any) As Long

Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (hpvDest As
Any, _
hpvSource As Any, ByVal cbCopy As Long)

Private Declare Sub CoTaskMemFree Lib "ole32.dll" (ByVal pv As Long)

Private Declare Function LocalAlloc Lib "kernel32" _
(ByVal uFlags As Long, _
ByVal uBytes As Long) As Long

Private Declare Function LocalFree Lib "kernel32" _
(ByVal hMem As Long) As Long


''The following declarations for the option to center the dialog in the
user's screen
Private Declare Function GetWindowRect Lib "user32" (ByVal hwnd As Long,
lpRect As RECT) As Long

Private Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As
Long) As Long

Private Const SM_CXFULLSCREEN = 16
Private Const SM_CYFULLSCREEN = 17

Private Type RECT
Left As Long
Top As Long
Right As Long
Bottom As Long
End Type

Private Declare Function MoveWindow Lib "user32" (ByVal hwnd As Long, _
ByVal x As Long, _
ByVal y As Long, _
ByVal nWidth As Long, _
ByVal nHeight As Long, ByVal bRepaint As Long) As Long
''End of dialog centering declarations

Function GetDirectory(InitDir As String, Flags As Long, CntrDlg As
Boolean, Msg) As String
Dim bInfo As BROWSEINFO
Dim pidl As Long, lpInitDir As Long

CntrDialog = CntrDlg ''Copy dialog centering setting to module
level variable so callback function can see it
With bInfo
.pidlRoot = 0 'Root folder = Desktop
.lpszTitle = Msg
.ulFlags = Flags

lpInitDir = LocalAlloc(LPTR, Len(InitDir) + 1)
CopyMemory ByVal lpInitDir, ByVal InitDir, Len(InitDir) + 1
.lParam = lpInitDir

If Val(Application.Version) 8 Then 'Establish the callback
function
.lpfn = BrowseCallBackFuncAddress
Else
.lpfn = AddrOf("BrowseCallBackFunc")
End If
End With
'Display the dialog
pidl = SHBrowseForFolder(bInfo)
'Get path string from pidl
GetDirectory = GetPathFromID(pidl)
CoTaskMemFree pidl
LocalFree lpInitDir
End Function

'Windows calls this function when the dialog events occur
Function BrowseCallBackFunc(ByVal hwnd As Long, ByVal Msg As Long, ByVal
lParam As Long, ByVal pData As Long) As Long
Select Case Msg
Case BFFM_INITIALIZED
'Dialog is being initialized. I use this to set the initial
directory and to center the dialog if the requested
SendMessage hwnd, BFFM_SETSELECTIONA, 1, pData 'Send
message to dialog
If CntrDialog Then CenterDialog hwnd
Case BFFM_SELCHANGED
'User selected a folder - change status text ("show status
text" option must be set to see this)
SendMessage hwnd, BFFM_SETSTATUSTEXTA, 0, GetPathFromID(lParam)
Case BFFM_VALIDATEFAILED
'This message is sent to the callback function only if
"Allow direct entry" and
'"Validate direct entry" have been be set on the Demo worksheet
'and the user's direct entry is not valid.
'"Show status text" must be set on to see error message we
send back to the dialog
Beep
SendMessage hwnd, BFFM_SETSTATUSTEXTA, 0, "Bad Directory"
BrowseCallBackFunc = 1 'Block dialog closing
Exit Function
End Select
BrowseCallBackFunc = 0 'Allow dialog to close
End Function

'Converts a PIDL to a string
Function GetPathFromID(ID As Long) As String
Dim Result As Boolean, Path As String * MAX_PATH
Result = SHGetPathFromIDList(ID, Path)
If Result Then
GetPathFromID = Left(Path, InStr(Path, Chr$(0)) - 1)
Else
GetPathFromID = ""
End If
End Function

'XL8 is very unhappy about using Excel 9's AddressOf operator, but as
long as it is in a
' function that is not called when run on XL8, it seems to allow it to
exist.
Function BrowseCallBackFuncAddress() As Long
BrowseCallBackFuncAddress = Long2Long(AddressOf BrowseCallBackFunc)
End Function

'It is not possible to assign the result of AddressOf (which is a Long)
directly to a member
'of a user defined data type. This explicitly "converts" it to a Long and
'allows the assignment
Function Long2Long(x As Long) As Long
Long2Long = x
End Function

'Centers dialog on desktop
Sub CenterDialog(hwnd As Long)
Dim WinRect As RECT, ScrWidth As Integer, ScrHeight As Integer
Dim DlgWidth As Integer, DlgHeight As Integer
GetWindowRect hwnd, WinRect
DlgWidth = WinRect.Right - WinRect.Left
DlgHeight = WinRect.Bottom - WinRect.Top
ScrWidth = GetSystemMetrics(SM_CXFULLSCREEN)
ScrHeight = GetSystemMetrics(SM_CYFULLSCREEN)
MoveWindow hwnd, (ScrWidth - DlgWidth) / 2, (ScrHeight - DlgHeight)
/ 2, DlgWidth, DlgHeight, 1
End Sub

---------------------------------------------------------------------------------
A second module (which happens to be called basAddrOf)
Option Explicit

'NOTE: The brilliant AddrOf function herein contained is the work of Ken
Getz and
'Michael Kaplan. Published in the May 1998 issue of
'Microsoft Office & Visual Basic for Applications Developer (page 46).

'Office 97 does not support the "AddressOf" operator which is needed to
tell Windows
'where our "call back" function is. Getz and Kaplan figured out a
workaround.

'The rest of this module is entirely their work.

'-------------------------------------------------------------------------------------------------------------------
' Declarations
'
' These function names were puzzled out by using DUMPBIN /exports
' with VBA332.DLL and then puzzling out parameter names and types
' through a lot of trial and error and over 100 IPFs in MSACCESS.EXE
' and VBA332.DLL.
'
' These parameters may not be named properly but seem to be correct in
' light of the function names and what each parameter does.
'
' EbGetExecutingProj: Gives you a handle to the current VBA project
' TipGetFunctionId: Gives you a function ID given a function name
' TipGetLpfnOfFunctionId: Gives you a pointer a function given its
function ID
'
'-------------------------------------------------------------------------------------------------------------------
Private Declare Function GetCurrentVbaProject _
Lib "vba332.dll" Alias "EbGetExecutingProj" _
(hProject As Long) As Long
Private Declare Function GetFuncID _
Lib "vba332.dll" Alias "TipGetFunctionId" _
(ByVal hProject As Long, ByVal strFunctionName As String, _
ByRef strFunctionId As String) As Long
Private Declare Function GetAddr _
Lib "vba332.dll" Alias "TipGetLpfnOfFunctionId" _
(ByVal hProject As Long, ByVal strFunctionId As String, _
ByRef lpfn As Long) As Long

'-------------------------------------------------------------------------------------------------------------------
' AddrOf
'
' Returns a function pointer of a VBA public function given its name.
This function
' gives similar functionality to VBA as VB5 has with the AddressOf
param type.
'
' NOTE: This function only seems to work if the proc you are trying to
get a pointer
' to is in the current project. This makes sense, since we are
using a function
' named EbGetExecutingProj.
'-------------------------------------------------------------------------------------------------------------------
Public Function AddrOf(strFuncName As String) As Long
Dim hProject As Long
Dim lngResult As Long
Dim strID As String
Dim lpfn As Long
Dim strFuncNameUnicode As String

Const NO_ERROR = 0

' The function name must be in Unicode, so convert it.
strFuncNameUnicode = StrConv(strFuncName, vbUnicode)

' Get the current VBA project
' The results of GetCurrentVBAProject seemed inconsistent, in our
tests,
' so now we just check the project handle when the function returns.
Call GetCurrentVbaProject(hProject)

' Make sure we got a project handle... we always should, but you
never know!
If hProject < 0 Then
' Get the VBA function ID (whatever that is!)
lngResult = GetFuncID( _
hProject, strFuncNameUnicode, strID)

' We have to check this because we GPF if we try to get a
function pointer
' of a non-existent function.
If lngResult = NO_ERROR Then
' Get the function pointer.
lngResult = GetAddr(hProject, strID, lpfn)

If lngResult = NO_ERROR Then
AddrOf = lpfn
End If
End If
End If
End Function







prepotency wrote:
Yes exactly. I guess my question in the OP was referring to the file
parsing method which uses the least memory (time taken to run). I had
considered using split or Mid but what we're really trying to get
around here is reading in the whole file. Furthermore with Split, it
takes so much time because it parses the whole file and writes its
contents into an array (the creation of the array is what takes so
long). With the Mid option, we are still forced to read on the whole
file. Here's a question: when you read in line by line does VBA still
open the whole file? I guess that's what I'm wondering. Because I
don't really want to open up the whole file. I just assumed that it
opened the whole file into ram and then started pulling the lines out
of it, which wouldn't necessarily save you any time.

Question: What's the fastest way to tab through every file in a
specified folder and how do you allow the user to browse and select a
folder (NOT a file)? Nagging question I was having.

G

prepotency Wrote:
I don't follow your terminology. You're saying to write a file compose
of all the file names and then read in from that file to find all th
files that the code will then parse? Please elaborate.

No,

There are a couple of ways to do this.

First you could move the file to a 'processed' directory, thereby onl
having files that need to be processed in the directory that you ar
scanning, or

When you use the DIR function to get the file name from the director
that you are about to process, before you actually process that fil
you generate a hash code from the file name.

A hash code is a number that is generated by a hashing algorithm.
(Check on the web using google...) The number generated would be a
almost unique number that identifies that file. You would use thi
number as a record pointer into a random access file.

That file might have a structure like:


Code
-------------------

Type Audit_Record
RecType as String * 2
Load_Date as Date
End Type

-------------------


When you then read that record using something like:


Code
-------------------

Get #Audit_File, File_Hash

-------------------


where File_Hash is the hash number generated for the file name, if th
file has not been processed then RecType will be blank.

After you have completed the processing for the current file then yo
would write a record to the Audit file with the values completed,
using the generated hash code as a record number.

In this way you would not only record when the file was processed, bu
also that it has actually been processed, which you can find ou
extrememely quickly without even opening the file.

For more on hash tables etc (even though it is in a differen
programming language) you could have a look a
http://sapsuperusers.com/phpBB2/viewtopic.php?t=503 which shows ho
hashing algorithms work.

Regards

Ric

--
Rich_
-----------------------------------------------------------------------
Rich_z's Profile: http://www.excelforum.com/member.php...fo&userid=2473
View this thread: http://www.excelforum.com/showthread.php?threadid=38663