Hi All,
I have this Excel VBA code which loops through the files in folder and saves them as .txt.
How do I use it in a Access form without the workbook reference?
I have this Excel VBA code which loops through the files in folder and saves them as .txt.
How do I use it in a Access form without the workbook reference?
Code:
Option Compare Text
Sub CopySheetToClosedWB()
LoopAllFolderAndSub ("C:\Users\PC\ Desktop\TEST\")
End Sub
Sub LoopAllFolderAndSub(ByVal FPath As String)
Dim FName As String, FullFPath As String, Folds() As String, FileNoExt As String
Dim i As Long, nFold As Long
Dim wb As Workbook, wbPrg As Workbook
Set wbPrg = ActiveWorkbook
Application.ScreenUpdating = False
If Right(FPath, 1) <> "\" Then FPath = FPath & "\"
FName = Dir(FPath & "*.*", vbDirectory)
While Len(FName) <> 0
If Left(FName, 1) <> "." Then
FullFPath = FPath & FName
If (GetAttr(FullFPath) And vbDirectory) = vbDirectory Then
ReDim Preserve Folds(0 To nFold) As String
Folds(nFold) = FullFPath
nFold = nFold + 1
Else
FileNoExt = Left(FullFPath, InStrRev(FullFPath, ".") - 1)
Name FullFPath As FileNoExt & ".txt"
End If
End If
FName = Dir()
Wend
For i = 0 To nFold - 1
LoopAllFolderAndSub Folds(i)
Next i
wbPrg.Close False
End Sub