PDA

View Full Version : Open csv file using VBA



daveaus
07-07-2010, 05:51 PM
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! : pray2:

Cheers,

mdmackillop
07-08-2010, 12:04 AM
Hi Dave,
Welcome to VBAX.
If it is not confidential, can you post the address of the file?

daveaus
07-09-2010, 10:59 PM
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!