CODEWITHSUNDEEP
vbaexcel
Yassin Hajaj
Yassin Hajaj

Reputation: 22005

Excel file does not get filled when opened by VBA

I'm writing a code to open a file and fill the files cells with the source file.

I'm encountering a problem that my second doesn't have its cells filled.

However, when the file is already open, it works perfectly.

I added a portion of code to make excel wait a few seconds before filling the second file but it does not work neither.

Have you an idea?

Code

Sub RemplirTableaux()

    Dim FilePath$, TargetFilePath$
    Const SheetName$ = "Sheet1"
    FilePath = "file.xlsx"
    TargetFilePath$ = "C:\file.xlsx"

    If Not IsWorkBookOpen(TargetFilePath) Then
        Workbooks.Open (TargetFilePath)
    End If

    Application.Wait DateAdd("s", 5, Now())

    For i = 7 To 23

        If Workbooks(FilePath).Sheets("ACs").Cells(i, "A") = Cells(1, "B") Then

            Workbooks(FilePath).Sheets("ACs").Cells(i, "B").Value = Cells(27, "J")
            Workbooks(FilePath).Sheets("ACs").Cells(i, "C").Value = Cells(27, "K")
            'Another filling statements
        End If
    Next
End Sub

Function IsWorkBookOpen(FileName As String)
    Dim ff As Long, ErrNo As Long

    On Error Resume Next
    ff = FreeFile()
    Open FileName For Input Lock Read As #ff
    Close ff
    ErrNo = Err
    On Error GoTo 0

    Select Case ErrNo
        Case 0:    IsWorkBookOpen = False
        Case 70:   IsWorkBookOpen = True
        Case Else: Error ErrNo
    End Select
End Function

Upvotes: 0

Views: 41

Answers (1)

Rory
Rory

Reputation: 34085

You need to properly qualify your Cells calls - for example:

Sub RemplirTableaux()

    Dim FilePath$, TargetFilePath$
    Dim wb                    As Workbook
    Dim wsSource              As Worksheet
    Const SheetName$ = "Sheet1"

    FilePath = "file.xlsx"
    TargetFilePath$ = "C:\file.xlsx"

    ' change as necessary
    Set wsSource = Workbooks("Tableau.xlsx").Sheets("Some sheet")

    If Not IsWorkBookOpen(TargetFilePath) Then
        Set wb = Workbooks.Open(TargetFilePath)
    End If

    For i = 7 To 23

        With wb.Sheets("ACs")

            If .Cells(i, "A").Value2 = wsSource.Cells(1, "B").Value2 Then
                .Cells(i, "B").Value = wsSource.Cells(27, "J").Value
                .Cells(i, "C").Value = wsSource.Cells(27, "K").Value
                'Another filling statements
            End If

        End With

    Next
End Sub

Upvotes: 2

Related Questions