Help needed : Open csv file in Box using VBA

[jimmywong98]

Hi Guys
Happy New Year! Does anyone know how to write a VBA script to open a CSV file in Box and copy out the data into excel sheet?
i have no idea how to write such scripting. Help needed.

thanks!

 

[jimmywong98]

Here are the scripts i used.. kindly guide

my idea is, using VBA to open a https link, download the content of the csv file to my macro workbook. From there i will perform my data processing.


Sub Macro1()


Dim Wb As Worksheets
Dim sFound As String
Dim path As String
path = "https://abc.app.box.com/files/0/f/11818289157/Customer%20Macro"
sFound = Dir(ActiveWorkbook.path & "\Book_*.xlsx")
If sFound <> "" Then
Set Wb = Workbooks.Open(Filename:=ActiveWorkbook.path & "" & sFound)
End If


With Wb.Sheets("Sheet1")
.Cells.Copy
End With


Workbooks("Book1.xlsm").Sheets("S").Range("A1").PasteSpecial Paste:=xlPasteValues
' VBA to close the source file
Application.CutCopyMode = False
Application.DisplayAlerts = False
Wb.Close
End Sub

However, error occurs as" Object variable or With block variable not set "

Can anyone help? Am still new in writing VBA scripts.

thanks!