Excel VBA if file closed then open and paste else just paste data

0 votes

After opening the CSV file, my VBA macros copy data from different tabs in a workbook to it. This element is operating properly. But before I open the CSV file and paste data into it, I want to make sure that it isn't already open. Just paste the data if it is already open.

Sub BU_Macro()



    Dim LR As Long, X As Long
    ThisWorkbook.Activate


    With Sheets("Report Group")

        LR = .Range("A" & .Rows.Count).End(xlUp).Row
        MyCopyRange = Array("A4:A" & LR, "B4:B" & LR, "C4:C" & LR, "D4:D" & LR) 'Put ranges in an array
        MyPasteRange = Array("A1", "B1", "C1", "D1")

        Dim myData As Workbook
        'open target csv file if not already opened
        If CheckFileIsOpen("test.csv") = False Then
            Set myData = Workbooks.Open(strFilePath & "test.csv")

        End If


        Worksheets("test").Select
        Sheets("test").UsedRange.Clear

        If LR > 1 Then
            j = 0
            For X = LBound(MyCopyRange) To UBound(MyCopyRange) 'Loop the array copying and pasting based on element in the array
                .Range(MyCopyRange(j)).Copy
                Sheets("test").Range(MyPasteRange(j)).PasteSpecial xlPasteValuesAndNumberFormats 'xlPasteValues
                j = j + 1
            Next

        Else
            Range("A1") = "No Data Found"
        End If

    End With

End Sub
Function CheckFileIsOpen(chkfile As String) As Boolean

    On Error Resume Next

    CheckFileIsOpen = (Workbooks(chkfile).Name = chkfile)

    On Error GoTo 0

End Function

If the file is closed, it opens it and pastes the date, But if the file is already open, I get an error:

Run-time error '9':
Subscript out of range
on line-
Worksheets("test").Select

I guess I am not being able to direct my code to focus on test.csv. Can someone please help me with this?

Sep 20 in Others by Kithuzzz
• 20,660 points
89 views

1 answer to this question.

0 votes

Slightly re-worked to add full workbook/sheet qualifiers and avoiding activate/select.

Sub BU_Macro()

    Dim LR As Long, X As Long, MyCopyRange, MyPasteRange, strFilePath
    Dim wb, myData As Workbook, shtPaste As Worksheet

    Set wb = ThisWorkbook

     'Put ranges in an array
    MyPasteRange = Array("A1", "B1", "C1", "D1")

    'open target csv file if not already opened
    If CheckFileIsOpen("test.csv") = False Then
        Set myData = Workbooks.Open(strFilePath & "test.csv")
    Else
        Set myData = Workbooks("test.csv")
    End If

    Set shtPaste = myData.Sheets("test")
    shtPaste.UsedRange.Clear

    With wb.Sheets("Report Group")

        LR = .Range("A" & .Rows.Count).End(xlUp).Row

        If LR > 1 Then
            MyCopyRange = Array("A4:A" & LR, "B4:B" & LR, "C4:C" & LR, "D4:D" & LR)
            'Loop the array copying and pasting based on element in the array
            For X = LBound(MyCopyRange) To UBound(MyCopyRange)
                .Range(MyCopyRange(X)).Copy
                shtPaste.Range(MyPasteRange(X)).PasteSpecial _
                           xlPasteValuesAndNumberFormats 'xlPasteValues
            Next
        Else
            shtPaste.Range("A1") = "No Data Found"
        End If

    End With

End Sub
answered Sep 21 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
2 answers
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

If you need to test a condition, ...READ MORE

answered Feb 17 in Others by gaurav
• 22,040 points
73 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18 in Others by gaurav
• 22,040 points
73 views
0 votes
1 answer

Runtime error 438 while importing data in excel from secured website using VBA

Replace With ieDoc.forms(0) .userType.Value = "1" ...READ MORE

answered Sep 23 in Others by narikkadan
• 37,660 points
105 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,720 points
452 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
2,222 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9 in Others by gaurav
• 22,040 points
69 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
208 views
0 votes
1 answer

Uipath(RPA) : read data from the PDF file and write to Excel file

If you want to use UiPath and ...READ MORE

answered Oct 17 in Others by narikkadan
• 37,660 points
55 views
0 votes
1 answer

Removing specific rows in an Excel file using Azure Data Factory

Under the 'Source' tab, choose the number ...READ MORE

answered Sep 23 in Others by narikkadan
• 37,660 points
234 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP