Rid the Immediate Window Call

E

Eskimo

Good Day,
I have this routine i found in a search that combines many CSVs into one.

Public Sub PrependFileNameAndConcatenate( _
ByVal TheFolder As String, _
OutputFile As String, _
Optional FileSpec As String = "*.csv")

'Reads all files in TheFolder that match FileSpec.
'Concatenates them to OutputFile, prepending the filename
'to each line.

Dim lngIn As Long
Dim lngOut As Long
Dim strFN As String
Dim strLine As String

lngOut = FreeFile()
Open OutputFile For Output As #lngOut

'Make sure folder name ends with \
If Right(TheFolder, 1) <> "\" Then
TheFolder = TheFolder & "\"
End If

strFN = Dir(TheFolder & FileSpec)
Do While Len(strFN) > 0 'loop through all files
lngIn = FreeFile()
Debug.Print "Processing " & strFN
Open TheFolder & strFN For Input As #lngIn
'enclose file name in quotes
strFN = """" & strFN & ""","
Do Until EOF(lngIn) 'loop through lines in files
Line Input #lngIn, strLine
Print #lngOut, strFN & strLine
Loop
Close #lngIn
strFN = Dir() 'get next filename
Loop
Close #lngOut
End Sub

but in order to to run this bit of code, I am needing to call this routine
with a call in the Immediate Window with this..


Call PrependFileNameAndConcatenate("C:\myCSVFiles\", "C:\temp\combined.txt")

Is there any way that I can just add this call procedure and the constant
file locations into the code and simply run it, without having to use the
immediate window? That way I can run a macro or button on a form to run the
routine.

Thanks,

Eskimo
 
G

Gina Whipp

Eskimo,

You can just add a button to your form and place your Call line on the
On_Click event. You might want to add a wee bit of error handling in case
the file is not in it's location. Something like...

If FileExists(C:\temp\combined.txt) Then
Call PrependFileNameAndConcatenate("C:\myCSVFiles\",
"C:\temp\combined.txt")
Else
Msgbox "Check for file... seems to be missing!"
End if

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
E

Eskimo

Hi Gina,

I understand that I can do that, but I am hoping to do away with the call
routine and hardwire the whole thing into the code. I tried to make changes,
but I am not experienced enough in the whoe vba thing.

Thanks anyway, but I would like to place the whole thing into one code. and
not use call procedures.

Thanks,

Eskimo
 
G

Gina Whipp

Eskimo,

My misunderstanding, you want to *hard* code the file so you don't have to
Call your function. May I ask why? Because leaving it the way it is allows
for file name changes, path changes. You can assign to a button and make it
so one can browse for the file.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
G

Gina Whipp

Eskimo,

'MAKE A BACK-UP MAKE A BACK-UP

Public Sub PrependFileNameAndConcatenate( _
ByVal TheFolder As String, _
OutputFile As String, _
Optional FileSpec As String = "*.csv")

'Reads all files in TheFolder that match FileSpec.
'Concatenates them to OutputFile, prepending the filename
'to each line.

Dim lngIn As Long
Dim lngOut As Long
Dim strFN As String
Dim strLine As String

lngOut = FreeFile()
'Open OutputFile For Output As #lngOut
Open "C:\temp\combined.txt" For Output As #lngOut

TheFolder = "C:\myCSVFiles\"

strFN = Dir(TheFolder & FileSpec)
Do While Len(strFN) > 0 'loop through all files
lngIn = FreeFile()
Debug.Print "Processing " & strFN
Open TheFolder & strFN For Input As #lngIn
'enclose file name in quotes
strFN = """" & strFN & ""","
Do Until EOF(lngIn) 'loop through lines in files
Line Input #lngIn, strLine
Print #lngOut, strFN & strLine
Loop
Close #lngIn
strFN = Dir() 'get next filename
Loop
Close #lngOut
End Sub

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
C

Clifford Bass via AccessMonster.com

Hi Gina,

That is bad. Some poor inexperience programmer will come along later
and think he can call that subroutine with certain parameters and not realize
those parameters will be ignored. If he wants to move the items into the
subroutine he should move the parameters into the procedure also as Consts:

Public Sub PrependFileNameAndConcatenate()

'Reads all files in TheFolder that match FileSpec.
'Concatenates them to OutputFile, prepending the filename
'to each line.

Const TheFolder As String = "C:\myCSVFiles
Const OutputFile As String = "C:\temp\combined.txt"
Const FileSpec As String = "*.csv"

Dim lngIn As Long
Dim lngOut As Long
Dim strFN As String
Dim strLine As String

lngOut = FreeFile()
Open OutputFile For Output As #lngOut
.....

My $0.02 worth,

Clifford Bass
 
G

Gina Whipp

Clifford,

Was just trying to give him a starting point. If you notice, I didn't want
to do this like this anyway, the code was fine fine the way it was. But I
will take my *50 lashes*...

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
E

Eskimo

Hi Gina,

Thanks for the response. I am setting this up for a group of polar bear and
caribou technicians that willl replace me and I am trying my best to make
this fool proof as much as possible.

I understand how the calls and lookups would make more sense, but I am
working to make the work as simple as possible and one less step to enter
achieves that simplicity. All of the file folders are constant and now your
new routine works great.

Thanks very much for your help.

Eskimo
 
C

Clifford Bass via AccessMonster.com

Hi Gina,

I did notice. 50 seems excessive. 1 would do I would think :)

Clifford Bass
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

update db, no errors , no changes 6
PLEASE HELP, insert 0
Immediate Window? 2
split, import data from array, error 9 1
Excel export formatting problem for fixed width files 1
Sending report as HTML Body of e-mail 1
The device 'Generic volume' cannot be stopped right now. Try stopping the device later. 0
Replacement solution including searching in subdirectories 0

Top