VBA - Doesn't copy from one workbook to other and active window not working

[BeginnerAtWork]

Hi forum members,

I have been at this macro for hours and unable to find out why it wont work, and from testing various available scripts on the internet this seems to be the fastest.

Objective - To download and open a file from a website i.e. http://www.asx.com.au/asx/research/ASXListedCompanies.csv
and on my current workbook "lets say Analysis.xlsx" worksheet "Summary" I am trying to open the downloaded file and copy information from ASXListedCompanies.csv into Summary then close ASXListedCompanies.csv and return back to the Summary worksheet. This is what I have, but it always opens the downloaded file and nothing is copied onto the Summary worksheet in the analysis workbook, please help!


Sub GetNewASXcompanies()
Dim myURL As String
myURL = "http://www.asx.com.au/asx/research/ASXListedCompanies.csv" 'direct http link


Dim WinHttpReq As Object
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", myURL, False
WinHttpReq.Send


myURL = WinHttpReq.ResponseBody
If WinHttpReq.Status = 200 Then
Set oStream = CreateObject("ADODB.Stream")
oStream.Open
oStream.Type = 1
oStream.Write WinHttpReq.ResponseBody
Kill ("C:\TEMP\file.csv")
oStream.SaveToFile ("C:\TEMP\file.csv")
oStream.Close
End If
ssheet = "Summary"
bgsheet = ("C:\TEMP\file.csv")
Set wnd = ActiveWindow
Application.ScreenUpdating = False
Sheets(ssheet).Cells.ClearContents
Workbooks.Open filename:=bgsheet
Windows(sfile).Activate
ActiveSheet.Cells.Copy
wnd.Activate
Sheets(ssheet).Paste
Application.DisplayAlerts = False
Windows(bgsheet).Close False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub