Open csv file using VBA

[daveaus]

Hi everyone,

I am needing to create a VBA code that downloads a csv file from a website so that data can be extracted to a main Excel file. However, my macro gets interrupted by the windows message asking me to either save or open the csv file.

Does anyone know how to create a macro that can open the file without prompting to save? I don't want to save the csv file; I just need it for a second.

I apologise if this is a stupid question... I am fairly new to VBA and its a steep learning curve! Please help!

Cheers,

[mdmackillop]

Hi Dave,
Welcome to VBAX.
If it is not confidential, can you post the address of the file?

[daveaus]

I have actually got my code to the stage where the data is imported using the Web Query function. However, the site is password protected and I need a way to either input the username and password for each time the macro runs or just once at the beginning. I am confused because I got it to work having logged into the site in IE, but the same doesn't seem to work anymore?!?!?! I am totally confused! My code is as follows:

Sub Download_Data()

Dim qryTable As QueryTable
Dim rngDestination As Range
Dim strConnection As String

' Define the connection string and destination range.
strConnection = "URL; Link which i cannot post due to forum blocks"
Set rngDestination = Sheet2.Range("A1")

' Create the QueryTable.
Set qryTable = Sheet2.QueryTables.Add(strConnection, rngDestination)

' Populate the QueryTable.
qryTable.WebSelectionType = xlSpecifiedTables
qryTable.WebTables = "7"
qryTable.WebFormatting = xlWebFormattingAll
qryTable.Refresh False

End Sub



Once again... any help would be much appreciated!