Solved Access VBA

[prasadgov]

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?

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

 

[Gasman]

I do not see where it even involves a workbook except for
Set wbPrg = ActiveWorkbook

Are you showing all the code?

 

[theDBguy]

I could be missing something, but I think you could just remove it. I don't see where you're actually using it anyway.

Edit: Oops, too slow...

 

[The_Doc_Man]

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?

VBA is VBA, whether it is the Access flavor or the Excel flavor or the Word flavor... because all of those "flavors" are just the sprinkles on the top. The differences between Access, Excel, Word, and other users of VBA are strictly in the application objects that they reference. Since none of the body of your loop seems to touch anything in that workbook, it should work in ANY environment that has references to the proper libraries. The only trick is that, like Excel and Word, Access VBA has to exist in a place where it can be called.

In Access this is the module, of which there are two kinds... general and "class" module. General Public modules can be called in various ways from queries and in macros. They can also be called from other general or class modules. Forms each have the potential for a single class module that can contain various event-related entry points. Events can include things that happen to bound forms. OR... you could create a command button on a form that could execute code in the form's class module or code in a general module.

I don't know how much you know about Access events, but there is where I would look for inspiration, including command button Click events.

 

[prasadgov]

I do not see where it even involves a workbook except for
Set wbPrg = ActiveWorkbook

Are you showing all the code?

you are right! I commented out the excel code and it works.