Excel file does not get filled when opened by VBA

Question

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

When you open the workbook it becomes active and your unqualified Cells calls refer to the active sheet (which is in the active workbook). Which sheet are you trying to copy from? — Rory
– Rory, Commented Feb 24, 2016 at 9:01
@Rory Hi Rory, thank you. Let's say I try to copy from "Tableau.xlsx" — Yassin Hajaj
– Yassin Hajaj, Commented Feb 24, 2016 at 9:09

Rory · Accepted Answer · 2016-02-24 09:13:29Z

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

Collectives™ on Stack Overflow

Excel file does not get filled when opened by VBA

Code

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Code

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related