Macro to refresh workbook, clear sheet, refer to path and import columns

[Retroshift]

Hi,
In my worksheet, I would like to add a macro which does the following at once: 1) refresh the active workbook, 2) clear the contents of a specific worksheet, 3) import columns data from a closed workbook, 4) refer to the path of the closed workbook based on cell data in the open workbook, 5) identify the columns data until the last row, and 6) add a messagebox.
Below is what I got so far but it is not complete. Does anyone know how to make the macro work? Many thanks in advance

Sub RefreshClearCopyColumnsData()

'First refresh the open workbook to update all the values

ActiveWorkbook.RefreshAll

'Then clear the contents of the worksheet in the open workbook

wkBk.Sheets("Sheet1").Cells.ClearContents

'Then get columns data from closed workbook into open workbook

Dim lastRow As Long
Dim myApp As Excel.Application
Dim wkBk As Workbook
Dim wkSht As Object

Set myApp = CreateObject("Excel.Application")

'Get path and Closed Workbook filename from (indirect?) cell A5 in open workbook

Set wkBk = myApp.Workbooks.Open("[(indirect) path reference from sheet2!A5 in open workbook]")

'Specify the columns until the last row with data

lastRow = wkBk.Sheets(1).Range("A,O,R,V,AD,AG,AH" & Rows.Count).End(xlUp).Row
wkBk.Sheets(1).Range("A1:A,O1:O,R1:R,V1:V,AD1:AD,AG1:AG,AH1:AH" & lastRow).Copy
myApp.DisplayAlerts = False
wkBk.Close
myApp.Quit
Set wkBk = Nothing
Set myApp = Nothing
Set wkBk = ActiveWorkbook
Set wkSht = wkBk.Sheets("Sheet1")
wkSht.Activate
Range("C2").Select
wkSht.Paste
Exit Sub

'Add messagebox

MsgBox “Data successfully imported”

End Sub

 

[rlv01]

Here is a variation.

Sub RefreshClearCopyColumnsData()
    Dim wkBk As Workbook, wkBk2 As Workbook
    Dim wkSht As Worksheet, wkSht2 As Worksheet
    Dim FullPathName As String
    Dim ColArr, ColLetter
    Dim rng As Range
    Dim I As Long, lastrow As Long
    
    'First refresh the open workbook to update all the values
    
    Set wkBk = ActiveWorkbook
    wkBk.RefreshAll
    
    'Then clear the contents of the worksheet in the open workbook
    Set wkSht = wkBk.Worksheets("Sheet1")
    wkSht.UsedRange.ClearContents
    
    'Then get columns data from closed workbook into open workbook
    'Get path and Closed Workbook filename from (indirect?) cell A5 in open workbook
    
    FullPathName = "[(indirect) path reference from sheet2!A5 in open workbook]"
    With CreateObject("Scripting.FileSystemObject")
        If Not .FileExists(FullPathName) Then
            MsgBox "File does not exist - abort"
            Exit Sub
        End If
    End With

    Set wkBk2 = Workbooks.Open(FullPathName)
    Set wkSht2 = wkBk2.Sheets(1)
    lastrow = wkSht2.UsedRange.Rows.Count
    
    'Specify the columns until the last row with data
    ColArr = Array("A", "O", "R", "V", "AD", "AG", "AH")
    
    For Each ColLetter In ColArr
        With wkSht2
            I = I + 1
            If I = 1 Then
                Set rng = Application.Intersect(.UsedRange, .Range(ColLetter & "1", .Range(ColLetter & lastrow)))
            Else
                Set rng = Application.Union(rng, Application.Intersect(.UsedRange, .Range(ColLetter & "1", .Range(ColLetter & lastrow))))
            End If
        End With
    Next ColLetter
    
    wkSht.Activate
    rng.Copy wkSht.Range("C2")
    wkBk2.Close False
    
    'Add message box
    MsgBox "Data successfully imported"
End Sub

 

[Retroshift]

Hi rlv01, I was wondering where and how in your code I have to fill in the cell that contains the full path name. And I also wonder if this code closes the closed workbook again?
Thanks

 

[rlv01]

Hi rlv01, I was wondering where and how in your code I have to fill in the cell that contains the full path name.

In your original code, you defined the path name this way:

Set wkBk = myApp.Workbooks.Open("[(indirect) path reference from sheet2!A5 in open workbook]")

My code uses the same construction:

  FullPathName = "[(indirect) path reference from sheet2!A5 in open workbook]"

Whatever you were going to put in for this: "[(indirect) path reference from sheet2!A5 in open workbook]", as your Workbooks.Open parameter to return a valid file path, use the same.

And I also wonder if this code closes the closed workbook again?

Yes. Here:

wkBk2.Close False

 

[Retroshift]

On sheet 2 of the open workbook, I have the path to workbook2 written in cell A5. It is C:\Myfolder\workbook2.xlsx

When I fill in "sheet2!A5" in

FullPathName = "[sheet2!A5]"

and in

Set wkBk2 = Workbooks.Open(sheet2!A5)

; it does not fetch the path name "C:\Myfolder\workbook2.xlsx" and therefore does not import the data from workbook 2 into sheet1 of the open workbook.

Excuse my VBA ignorance

 

[Retroshift]

Maybe important to add: I cannot type the path name directly into the VBA code because it needs to be fetched from the Excel sheet itself as the path name is dynamic and can change after refreshing the sheet.

Knowing this, how do I make the code fully work in this respect? Thanks

 

[rlv01]

The statement would be

 FullPathName = [Sheet2!A5].Value

But since I am not a fan of that kind of [ ] bracket notation, I would write it like this

 FullPathName = wkBk.Worksheets("Sheet2").Range("A5").Value

 

[Retroshift]

You sir, are a genius. The code did the trick. Thanks a lot for all the help and advice.

 

[Retroshift]

One last question: I was wondering what code I have to add to only import the first 10 characters of the data in column O?

 

[rlv01]

One last question: I was wondering what code I have to add to only import the first 10 characters of the data in column O?

Better to erase the cells you don't want after the copy operation.

    wkSht.Activate
    rng.Copy wkSht.Range("C2")

    wkSht.Range("C2").Offset(11, 1).Resize(lastrow - 11, 1).ClearContents
    
    wkBk2.Close False

    'Add message box
    MsgBox "Data successfully imported"