Rid the Immediate Window Call

[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

 

[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

 

[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

 

[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

 

[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

 

[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

 

[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

 

[Gina Whipp]

Eskimo,

Ahhh, explains the name! You are welcome, though Clifford made a good point
but in your case it may not apply!

--
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

 

[Gina Whipp]

Clifford,

Thanks...

--
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