how to convert VBA written for excel to access

[govblaine]

I need help, I found some VBA from an excel workbook that I want to be able to use in access. It currently works fine from the excel workbook but I need to use it in access. I have no experience with VBA in Access and I can only think the same code can be used but needs to be tweaked for access. The code looks for information inside files in a folder and renames the files based on the value found at a certain position in the file. It also adds he extension ".txt". Can anyone help make this code work in access?

Thank you

Function RenameTextFiles()
Dim fso As FileSystemObject
Dim x As Integer
Dim strPortCode As String
Dim strfile As String
Dim TextLine
Dim strFullFileName As String
Dim FILEPATH As String
Dim FILEPATH2 As String

FILEPATH = "C:\Data\"
FILEPATH2 = "C:\TempFiles\"

Dim pathcrnt As String, batch file As Integer
pathcrnt = ActiveWorkbook.Path
batch_file = FreeFile()
Set fso = CreateObject("Scripting.FileSystemObject")
strfile = Dir(FILEPATH)
 
Do While strfile <> ""
  x = 1
  strFullFileName = FILEPATH & strfile
  Open strFullFileName For Input As #1 ' Open file.
  Do While Not EOF(1) ' Loop until end of file.
    Line Input #1, TextLine ' Read line into variable.
    If x = 48 Then
      strPortCode = Mid(TextLine, 18, 4)
      Exit Do
    End If
    x = x + 1
  Loop
  Close #1 ' Close file.
  If Left(fso.GetFileName(strfile), 5) = "PCOSS" Then
    If Not fso.FileExists(FILEPATH & "COSS" & strPortCode & ".txt") Then
        Name FILEPATH & strfile As FILEPATH & "COSS" & strPortCode & ".txt"
    End If
  End If
  strfile = Dir
Loop
 
 
End Function

[ranman256]

It should work the same for access, unless you have Excel objects.
which Is the workbook.. This is all file ops.
paste into a module.

[ItsMe]

Yeah, I don't see anything that is Excel specific, either.

[ssanfu]

You will probably need to set a reference to "Microsoft Scripting RunTime".

Dim pathcrnt As String, batch file As Integer

"batch file" has a space in the name. There is a line

batch_file = FreeFile()

but it is not used, so both lines can be removed/commented out.
Also, the line

pathcrnt = ActiveWorkbook.Path

should be removed/commented out. Access does not have an "ActiveWorkbook"

Should also probably add

Set fso = Nothing

after the last "Loop"

    Loop

    Set fso = Nothing

End Function

[govblaine]

You will probably need to set a reference to "Microsoft Scripting RunTime".

Dim pathcrnt As String, batch file As Integer

"batch file" has a space in the name. There is a line

batch_file = FreeFile()

but it is not used, so both lines can be removed/commented out.
Also, the line

pathcrnt = ActiveWorkbook.Path

should be removed/commented out. Access does not have an "ActiveWorkbook"

Should also probably add

Set fso = Nothing

after the last "Loop"

    Loop

    Set fso = Nothing

End Function

ssanfu, I made all the changes you suggested to cleaned up the code and set the reference. now the code seems to work. Thank you all very much for your time and responses.

[June7]

I ran the code as a Sub instead of Function - no error.