Results 1 to 2 of 2
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Manual Links (EXCEL 97/2000)

    Occasionally I want to update a worksheet with data from a master-sheet. Normally, one could think of two ways to do this:
    [1] Insert links
    [2] Type data in manually (or manually copy & paste-as-values)

    Obviously, [2] is not an option. The first possibility is also not nice as the update will always take place and the sheet keeps on prompting to 'update links'.

    What I wanted to implement is a button which a user could click. The VBA code called, then does the copy & paste-as-values or so. This would allow the user to update links (I could even protect that with a password) and still there wouldn't be any 'live-links' in the sheet.
    Obviously, this isn't very hard to do but I'm trying to avoid re-inventing the wheel here. Anybody out there with pieces of code that do just that?

    Thanks,

    Erik Jan

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Manual Links (EXCEL 97/2000)

    Assuming:

    Range to get the data into is called "InputRange"
    Range to get data from is called "OutputRange"

    Untested!!

    Sub GetDataFromSource()
    Dim wSourceWorkbook as Workbook
    Dim wDestWorkbook as Workbook
    Set wDestworkbook=Activeworkbook
    Workbooks.Open FileName:="PathToSourceNameOfSource.xls"
    Set wSourceWorkbook=ActiveWorkbook
    wDestWorkbook.[InputRange].Value=wSourceWorkbook.[OutputRange].Value
    wSourceWorkbook.Close False
    Set wSourceWorkbook=Nothing
    Set wDestWorkbook=Nothing
    ENd Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •