2003 macro to 2007

[wmbb]

I use the following macro in acces 2003
In acces 2007 it doesn't work

Sub ReadTxtImport()
On Error GoTo Err_ReadTxtImport

Dim fs As FileSystemObject, fd As Folder, fc As Files, f As File, ts As TextStream '' Variables for FileSystemObject
Dim rst As DAO.Recordset
Dim strFolder As String, strFile As String
Dim strLine As String, strLineReturn As String
Dim strDetector As String
Dim Magnification1 As Long, Magnification2 As Long
Dim XPos As Long, Ypos As Long
Dim HighTension As Integer, Spot As Currency

'No warnings
DoCmd.SetWarnings False

DoCmd.RunSQL "DELETE * FROM tblTextFiles"

'Warnings are back
DoCmd.SetWarnings True

Form_Menu.report1.Enabled = False
Form_Menu.report2.Enabled = False
Form_Menu.report3.Enabled = False

strFolder = PickFolder("R:\SEM-FIB\Analyseresultaten")
Forms!Menu!txtfolder = strFolder

Set fs = CreateObject("Scripting.FileSystemObject")
Set fd = fs.GetFolder(strFolder)
Set fc = fd.Files

    For Each f In fc
                              
      If Left(f.Name, 1) = "_" Then
      Set ts = f.OpenAsTextStream(1, -2)
      Set rst = CurrentDb.OpenRecordset("tblTextFiles")
        
                For i = 1 To 40
                strFile = f.Name
                strLine = ts.ReadLine
                strLineReturn = Mid(strLine, InStr(strLine, "=") + 2)

                                               
                        If Left(strLine, 6) = "flSpot" Then
                
                            Spot = Val(strLineReturn)
                            
                        ElseIf Left(strLine, 6) = "flMagn" Then
                
                            Magnification1 = 46.5 / Val(strLineReturn)
                            
                        ElseIf (Left(strLine, 8) = "lDetName" And Val(strLineReturn) = 0) Then
                
                            strDetector = "SE"
                        
                        ElseIf (Left(strLine, 8) = "lDetName" And Val(strLineReturn) > 0) Then
                
                            strDetector = "BSE"

                        ElseIf Left(strLine, 16) = "flStageXPosition" Then
        
                            XPos = Val(strLineReturn) / 1000
                            
                        ElseIf Left(strLine, 16) = "flStageYPosition" Then
        
                            Ypos = Val(strLineReturn) / 1000
                            
                        ElseIf Left(strLine, 13) = "Magnification" Then
        
                            Magnification2 = Val(strLineReturn)
                            
                        ElseIf Left(strLine, 11) = "HighTension" Then
        
                            HighTension = Val(strLineReturn) / 1000
                            Debug.Print strFile, strDetector, Magnification1, Magnification2, HighTension, Spot, XPos, Ypos
                                                With rst

                                .AddNew

                                !FileName = strFile
                                !Detector = strDetector
                                !Magnification1 = Magnification1
                                !Magnification2 = Magnification2
                                !HighTension = HighTension
                                !Spot = Spot
                                !XPos = XPos
                                !Ypos = Ypos

                                .Update

                            End With
                            strFile = ""
                            strDetector = ""
                            Magnification1 = 0
                            Magnification2 = 0
                            HighTension = 0
                            Spot = 0
                            XPos = 0
                            Ypos = 0
                            
                        End If
                

          Next i
        Form_Menu.report1.Enabled = True
        Form_Menu.report2.Enabled = True
        Form_Menu.report3.Enabled = True
      End If
    Next

Exit_ReadTxtImport:

ts.Close
rst.Close

Set rst = Nothing
Set ts = Nothing
Set fc = Nothing
Set fd = Nothing
Set fs = Nothing

Exit Sub

Err_ReadTxtImport:

Debug.Print Err.Number, Err.Description

End Sub
Public Function PickFolder(strStartDir As Variant) As String
Dim SA As Object, f As Object
Set SA = CreateObject("Shell.Application")
Set f = SA.BrowseForFolder(0, "Choose a folder", 16 + 32 + 64, strStartDir)
If (Not f Is Nothing) Then
  PickFolder = f.Items.Item.Path
End If
Set f = Nothing
Set SA = Nothing
End Function

What do I have to do to get it work in acces 2007 ?

 

[oharab]

I've not used VBA in 2007 yet, but can you be a little more specific with what you mean by "It Won't Work".

Cheers

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.

 

[MajP]

Ditto above.
But my first guess would be check your references
Does it break here?
Dim fs As FileSystemObject