VBA Run time - Please help =>Code attached

[101287]

When I execute the code below on the second iteration reading the Excel file I get run-time error 1004.

Can someone help me correct this problem? I don't understand what I'm missing.

Thank you - Luis

===============CODE ============================================
Option Compare Database
Public HoldRequirement As String
Public counter As Integer
Public activeCnt As Integer

Public parseproject As String

Sub ReadFileToProcess()
'Read the directory of the folder that contains the
'files to be loaded
activeCnt = 0
Dim fPathDirectory As String, fName As String
Dim fileLoaded1 As String, filesUploadedcnt As Integer
Dim tblProjectsAndRequirements As String, debugFlag As Boolean
debugFlag = True

'The Name of the table that the records are going to be stored
tblProjectsAndRequirements = "cpyProjectsAndRequirements"
filesUploadedcnt = 0
fPathDirectory = "F:\EVMSFiles\ITPlanningRequirements\"
fName = Dir(fPathDirectory, vbDirectory) ' Retrieve the first entry
DoCmd.Hourglass True
Do While fName <> "" ' Start the Loop
If fName <> "." And fName <> ".." Then
If Left(fName, 3) = "200" Then
If debugFlag = True Then
Debug.Print "Path Name= " & fPathDirectory & "File Name=" & fName
filesUploadedcnt = filesUploadedcnt + 1
Dim xlsApp As Excel.Application
Dim xlswkb As Excel.Workbook
Set xlApp = New Excel.Application
'ActiveSheet.Cells.MergeCells = False
With xlApp
.Visible = True
Set xlWB = .Workbooks.Open("F:\EVMSFiles\ITPlanningRequirements\" & fName, , False)
If activeCnt = 0 Then
activeCnt = activeCnt + 1
ActiveSheet.Cells.MergeCells = False
End If
End With
Call FormatRequirement

xlApp.Quit
Set xlsApp = Nothing
Set xlWB = Nothing
Set xlswkb = Nothing
Set xlsApp = Nothing
Set xlApp = Nothing
fileLoaded1 = fileLoaded1 & fName & " "
DoCmd.Hourglass False
Else
DoCmd.Hourglass True
DoCmd.Hourglass False
End If
End If
End If
fName = Dir
Loop
MsgBox "Files Loaded: " & filesUploadedcnt, , "JSF Projects and Requirements"

End Sub
Sub FormatRequirement()
Range("A3").Select '---SECOND ITERATION ABEND - Run time error 1004
counter = 0
HoldRequirement = Sheets(1).Range("a3").Value
Do Until counter > 550
Call fillRequirement
Loop
' Insert column for the Project

 

[PHV]

You have to fully qualify each reference to an excel object.
See your other post thread181-1282061 for more details.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886