Macro to import data from another spreadsheet

[graduate106]

Hi sorry if this has been asked before but i can not find the info i need.

I basically have a spreadsheet which works out somebody's pension. This spreadsheet is constantly being updated so every time i want to put a client onto the new version of the spreadsheet i need to re-enter all of their details.

I want to create an "Import" button on the master spreadhseet which then opens up a window to browse for a file. I want to be able to then choose the clients saved spreadsheet (which is basically an old proforma) and once i have chosen the file i want it to import all of the client details, ie, copy 100 or so different boxes from the old spreadsheet onto the new one. eg. B5 to B5, C7 to C7 etc.

So, is this actually possible? And if so i would be very grateful for any help.

Cheers
Simon

 

[graduate106]

Or if this can't be done, a suitable alternative would be sufficient.

Thanks again

 

[Colo]

Hi, here is a sample code for you. You can assign this code to the import button on the master worksheet.

Dim OpenFileName As String
Dim wb As Workbook
'Select and Open workbook
OpenFileName = Application.GetOpenFilename("clients saved spreadsheet,*.xls")
If OpenFileName = "False" Then Exit Sub
Set wb = Workbooks.Open(OpenFileName)

'Get data EXAMPLE
ThisWorkbook.Sheets(1).Range("B5:C7").Value = wb.Sheets(1).Range("B5:C7").Value

MsgBox ("Done")

 

[graduate106]

Hi, here is a sample code for you. You can assign this code to the import button on the master worksheet.

Dim OpenFileName As String
Dim wb As Workbook
'Select and Open workbook
OpenFileName = Application.GetOpenFilename("clients saved spreadsheet,*.xls")
If OpenFileName = "False" Then Exit Sub
Set wb = Workbooks.Open(OpenFileName)

'Get data EXAMPLE
ThisWorkbook.Sheets(1).Range("B5:C7").Value = wb.Sheets(1).Range("B5:C7").Value

MsgBox ("Done")

That is absolutely superb, thank you Colo.

Can an extra bit of code be entered to close the source file once the import has been done? (currently the old file remains open as well as the new one once the macro has run)

 

[graduate106]

Skip that actually, just added wb.close at the bottom.

My other "problem" is that these old workbooks have links to other workbooks and i get the annoying "do you want to update" message pop up when i import from the file. Can i get it to just choose the "do not update" option to prevent the user from having to click this everytime?

 

[Colo]

All right, graduate106.
You can ignore that message by adding this one when the target workbook is opened.

UpdateLinks:=0

Set wb = Workbooks.Open(OpenFileName, UpdateLinks:=0)

 

[graduate106]

All right, graduate106.
You can ignore that message by adding this one when the target workbook is opened.

UpdateLinks:=0

Set wb = Workbooks.Open(OpenFileName, UpdateLinks:=0)

Colo, that worked fantastic thank you!

One final query then i will leave you alone, is there some similar code i can enter to prevent it from asking me to save the date source file when it is closed?