Import data table from website into workbook upon opening

[nuc_n_futs]

Hi,

Forgive me if this has been asked and answered, I couldn't quite find what I was looking for when I did a search.

I want my workbook to automatically go to the website and import the data into a sheet on my workbook once the workbook is opened.

The website already has a button for exporting the data to excel
I will be assigining the data to the same worksheet
I only want to import the data the first time the workbook is opened. I most likely will make the workbook "read ony" and it will have to be renamed so the if the name of the workbook is tied into the code so it will not download again once the file name has changed, that would be excellent.

I am not the best at VBA code so a step by step would be helpful.

Thank you,

Nuc

 

[nuc_n_futs]

Can anyone help please?

Thank you

 

[John_w]

I want my workbook to automatically go to the website and import the data into a sheet on my workbook once the workbook is opened.

The website already has a button for exporting the data to excel

Call the import data routine from the Workbook_Open event, and set/check a value in a cell to decide if the data has already been downloaded, or check for some value in the downloaded data. However I can't help further without knowing the URL and exactly which data you want to import.

 

[nuc_n_futs]

The URL is: http://www.gsa.gov/portal/category//21287

On the right hand side, there is a link for downloadable resources:

"FY2011 Per Diem Rates (Revised)"

Yes, definetely will go in the workbook open event.

The reason I only want the data to download when it is first opened is because this will be used for an exepense report. My thought was to provide a "Read Only" master file that will be renamed once the user has completed the form so if it is reopened, the data will not download again because at that point it has been already been completed and I wouldn't want it to recalculate again, so I am suggesting it will first look at the Excel workbook name (master file) first. If the workbook name has changed, it will do nothing.

Nuc

 

[John_w]

I'm a bit confused about some parts of your posts. You talk about the web site having a button for exporting to Excel, but the "FY2011 Per Diem Rates (Revised)" link in your 3rd post is a link, not a button. Also, I'm not sure what you mean by 'user has completed the form'; do you mean the web page form? If so, why do you need to complete this form when the data is directly available from the .xls download link?

Anyway, I've tried to interpret your requirements the best I can, and the following code should get you started. It checks whether a sheet called 'Master' exists in the workbook, and if not creates it and downloads the .xls workbook into it. The code goes in the ThisWorkbook module.

Option Explicit

Private Sub Workbook_Open()
    
    If Not SheetExists("Master") Then
        Download_Data
    End If
    
End Sub

Private Sub Download_Data()
        
    Dim masterSheet As Worksheet
    
    Sheets.Add after:=Sheets(Worksheets.Count)
    Set masterSheet = Sheets(Sheets.Count)
    masterSheet.Name = "Master"

    Workbooks.Open fileName:="http://www.gsa.gov/graphics/ogp/FY2011PerDiemRatesRevision4111.xls"
    ActiveWorkbook.Sheets(1).Cells.Copy masterSheet.Range("A1")
    ActiveWorkbook.Close SaveChanges:=False
    
End Sub


Private Function SheetExists(SheetName As String) As Boolean
    SheetExists = False
    On Error Resume Next
    SheetExists = (Len(Sheets(SheetName).Name) > 0)
End Function

 

[nuc_n_futs]

Thanks John, I will give this a go.

Sorry about any confusion. when I referred to a "button" on the website, I was basing that on memory from a project that started 2 years ago but was squashed. When I first started this post, I did not have the web address. There were changes made to the webiste obviously.

In regards to when the user completes the form, I meant the Excel form. I only need to pull in the download to make sure the calculations are based on the latest and greatest data.

Nuc