Solved

excel and updating links on another excel sheet

Posted on 2012-04-02
20
217 Views
Last Modified: 2012-04-14
i have three workbook a, b,c  a workbook is updated daily and gets renamed each day, eg a24-4-2012 then a25-4-2012  this has numbers that i need to access on workbook c.
so i was going to use workbook b to store the numbers from a for the time that when excel workbook c wants them then it will be linking to the same range names on workbook b all the time.
now what i am thinking of doing is to have range names set up on workbook a, which the name will change and rows and colums will be inserted, but these wil not change the range name positions, and when they are finished the workbook will then up date the data on workbook B so when workbook a has its name changed it will not affect the data as it  will have updated the data on workbook b whos name will remain the same.

end of day sheet a saves to workbook b
workbook c picks up its data from workbook b
and all the data arrives correctly

so what i need is a reverse link, the save updates the range name on workbook range name b and this is the code that i am looking for

workbook.b.rangename "rbr-45" = workbook.a.rangename "rbr-45"

any ideas on the code woudl be very helpfull or alternate ideas if this is going down the wrong track

***********************
this get statment gets the value from the correct sheet but what i need is the opposite a put value on the page

Private Function GetValue(path, file, sheet, ref)
'   Retrieves a value from a closed workbook
    Dim arg As String
'   Make sure the file exists
    If Right(path, 1) <> "\" Then path = path & "\"
    If Dir(path & file) = "" Then
        GetValue = "File Not Found"
        Exit Function
    End If
'   Create the argument
    arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
      Range(ref).Range("A1").Address(, , xlR1C1)
'   Execute an XLM macro
    GetValue = ExecuteExcel4Macro(arg)
End Function
0
Comment
Question by:sydneyguy
  • 11
  • 9
20 Comments
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
>> workbook.b.rangename "rbr-45" = workbook.a.rangename "rbr-45"

Assuming workbook A and workbook B are open at the time this macro runs (in Workbook B - a public module).  The below works on range names that refer to range addresses, as opposed to other values, etc:

Option Explicit

Sub resetRangeAtoB()
Dim wkbA As Workbook
Dim wkbB As Workbook
Dim myName As Name

    Set wkbB = ThisWorkbook
    Set wkbA = Workbooks("Workbook A r1.xls") '<- change workbook name for workbook A
    
    For Each myName In wkbA.Names
        On Error Resume Next
        wkbB.Names.Add Name:=myName.Name, RefersTo:=Range("'[" & wkbA.Name & "]" & myName.RefersToRange.Worksheet.Name & "'!" & myName.RefersToRange.Address)
        If Err.Number <> 0 Then
            MsgBox "Could not generate name: " & myName & " in Workbook B." & vbCrLf & "Tried to add: " & vbCrLf & "'[" & wkbA.Name & "]" & myName.RefersToRange.Worksheet.Name & "'!" & myName.RefersToRange.Address, vbOKOnly, "Continuing process..."
        End If
        On Error GoTo 0
    Next myName
    
End Sub

Open in new window


Cheers,

Dave
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
That's a bit over the top.  It occurred to me that if the RefersTo:= a range, then the actual range object should suffice, resolving to address Workbook A properly, and it does.  This code is a bit simpler to read, but along the same lines:

Sub resetRangeAtoB()
Dim wkbA As Workbook
Dim wkbB As Workbook
Dim myName As Name

    Set wkbB = ThisWorkbook
    Set wkbA = Workbooks("Workbook A r1.xls") '<- change workbook name for workbook A
    
    For Each myName In wkbA.Names
        On Error Resume Next
        Debug.Print myName.Name
        wkbB.Names.Add Name:=myName.Name, RefersTo:=myName.RefersToRange
        If Err.Number <> 0 Then
            MsgBox "Could not generate name: " & myName.Name & " in Workbook B." & vbCrLf & "Tried to add: " & vbCrLf & myName.Name & ": " & myName.RefersTo, vbOKOnly, "Continuing process..."
        End If
        On Error GoTo 0
    Next myName
    
End Sub

Open in new window

Dave
0
 

Author Comment

by:sydneyguy
Comment Utility
thanks for that dave, still struggling with the correct syntax though
workbook a is and lives on  drive "I:\PAS\Project List and Budget 2-04-12.xlsm"
workbook b is and lives on drive  "I:\ProjectsAndSurvey\DataTransfer.xlsm"

i am going to get one range name on workbook b to change from code on workbook a (Project List and Budget) via a vb macro then once i have that i can put your code into operation and then do all the range names and that would do the perfect job.

if you could help me with this one line of correct syntax it would be fantastic
garry
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Here's the updated code which you can run from any workbook, as long as its in a public module, as workbook A and B are referenced as you suggest.
Sub resetRangeAtoB()
Dim wkbA As Workbook
Dim wkbB As Workbook
Dim myName As Name

    
    Set wkbB = Workbooks.Open(Filename:="I:\ProjectsAndSurvey\DataTransfer.xlsm")
    Set wkbA = Workbooks.Open(Filename:="I:\PAS\Project List and Budget 2-04-12.xlsm")
    
    For Each myName In wkbA.Names
        On Error Resume Next
        Debug.Print myName.Name
        wkbB.Names.Add Name:=myName.Name, RefersTo:=myName.RefersToRange
        If Err.Number <> 0 Then
            MsgBox "Could not generate name: " & myName.Name & " in Workbook B." & vbCrLf & "Tried to add: " & vbCrLf & myName.Name & ": " & myName.RefersTo, vbOKOnly, "Continuing process..."
        End If
        On Error GoTo 0
    Next myName
    
End Sub

Open in new window


>>i am going to get one range name on workbook b to change from code on workbook a (Project List and Budget) via a vb macro

I do not understand what you mean by this.  Do you need help with this?  I need further clarification - please be specific.

Dave
0
 

Author Comment

by:sydneyguy
Comment Utility
yes please
i should be able to bring this down to one line which is what i am trying to do at the moment


******** PBI_SOTC  is the rang name i am modifing

function update



  Set wkbB = Workbooks.Open(Filename:="I:\ProjectsAndSurvey\DataTransfer.xlsm")
  Set wkbA = Workbooks.Open(Filename:="I:\PAS\Project List and Budget 2-04-12.xlsm")
    this is fine
but doing it for just one range this is what i woudl like some help with for
so the line below should change the loc PBI_SOTC  on wkbB range name run form wkbA with the data from PBI_SOTC  on wkbA

  wkbB.Names.Add Name:=myName.Name, RefersTo:=myName.RefersToRange
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Understood.

Sub resetRangeAtoB()
Dim wkbA As Workbook
Dim wkbB As Workbook
Dim myName As Name

    
    Set wkbB = Workbooks.Open(Filename:="I:\ProjectsAndSurvey\DataTransfer.xlsm")
    Set wkbA = Workbooks.Open(Filename:="I:\PAS\Project List and Budget 2-04-12.xlsm")
    
    Set myName = wkbA.Names("PBI_SOTC")
        
    On Error Resume Next

    wkbB.Names.Add Name:=myName.Name, RefersTo:=myName.RefersToRange
    If Err.Number <> 0 Then
        MsgBox "Could not generate name: " & myName.Name & " in Workbook B." & vbCrLf & "Tried to add: " & vbCrLf & myName.Name & ": " & myName.RefersTo, vbOKOnly, "Continuing process..."
    End If
    
    'clean up, if necessary
    wkbA.Close SaveChanges:=False
    wkbB.Close SaveChanges:=True
End Sub

Open in new window


Dave
0
 

Author Comment

by:sydneyguy
Comment Utility
still no go if wkba is open the line
Set wkbA = Workbooks.Open(Filename:="I:\PAS\Project List and Budget 2-04-12.xlsm")
will try to re open the sheet and this is were the code is running from so this line i believe will not operate as intended

so i am simply just trying to get the syntax that will from wkba modify location

Dim wkbA As Workbook
Dim wkbB As Workbook
Dim myName As Name


    Set wkbB = Workbooks.Open(Filename:="I:\WoronoraForecast\bookb.xls")
   
    Set wkbA = ActiveWorkbook '.Workbooks.Open(Filename:="I:\WoronoraForecast\bookb.xls")
   
Range("PBI_SOTC").Select
   
   Set myName = wkbA.Names("PBI_SOTC")

 
    **************************************
this is the simple line that i am now trying to get working
were wksb = some number "3433443"  so that at least i can get and see some syntax actually talking to the other sheet  any ideas please the line below does not work but am trying to get it running

    wkbB.Sheets(Sheet1).ActiveSheet = "3433443"    

*******************************************
wkbB.Names.Add Name:=myName.Name, RefersTo:=myName.RefersToRange
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
Comment Utility
Ok.  I wrote that assuming that workbook A and workbook B would be closed.  Note the last lines in my example actually close those files.

However, here is the revised code. You might comment out the workbook close methods at the bottom of the code for testing.

Option Explicit

Sub resetRangeAtoB()
Dim wkbA As Workbook
Dim wkbB As Workbook
Dim myName As Name

Dim fileA As String
Dim fileB As String

    fileA = "I:\PAS\Project List and Budget 2-04-12.xlsm"
    fileB = "I:\ProjectsAndSurvey\DataTransfer.xlsm"
    
    On Error Resume Next
    Set wkbA = Workbooks(fileA)
    If Err.Number <> 0 Then 'need to open the file
        Err.Clear
        Set wkbA = Workbooks.Open(Filename:=fileA)
        If wkbA Is Nothing Then
            MsgBox "Could not access file: " & fileA
            Exit Sub
        End If
    End If
    
    Set wkbB = Workbooks(fileB)
    If Err.Number <> 0 Then 'need to open the file
        Err.Clear
        Set wkbB = Workbooks.Open(Filename:=fileB)
    End If
    
    If wkbB Is Nothing Then
        MsgBox "Could not access file: " & fileB
        Exit Sub
    End If
    On Error GoTo 0
    
    
    Set myName = wkbA.Names("PBI_SOTC")
        
    On Error Resume Next

    wkbB.Names.Add Name:=myName.Name, RefersTo:=myName.RefersToRange
    If Err.Number <> 0 Then
        MsgBox "Could not generate name: " & myName.Name & " in Workbook B." & vbCrLf & "Tried to add: " & vbCrLf & myName.Name & ": " & myName.RefersTo, vbOKOnly, "Continuing process..."
    End If
    
    'clean up, if necessary
    wkbA.Close SaveChanges:=False
    wkbB.Close SaveChanges:=True
End Sub

Open in new window

0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
**************************************
this is the simple line that i am now trying to get working
were wksb = some number "3433443"  so that at least i can get and see some syntax actually talking to the other sheet  any ideas please the line below does not work but am trying to get it running

    wkbB.Sheets(Sheet1).ActiveSheet = "3433443"    

*******************************************

What is it you want to do?  activate sheet "3433443" ??

Wkb.Sheets("3433443").Activate '<- makes 3433443 the active sheet.

How does this relate to the problem we're trying to solve with this question?

Dave
0
 

Author Comment

by:sydneyguy
Comment Utility
no not trying to access sheet "3433443"  
same problem as before, have run your new code it all steps through made a couple of small mods as its still fell over for what ever reason, but it steps through
 MsgBox (myName.RefersToRange) show the value of 1 which it is set to on the sheet
but line
 wkbB.Names.Add Name:=myName.Name, RefersTo:=myName.RefersToRange
still does not place the data on to wkbB as i believe it should

have included the files but it must be close


Option Explicit

Sub resetRangeAtoB()
Dim wkbA As Workbook
Dim wkbB As Workbook
Dim myName As Name

Dim fileA As String
Dim fileB As String

    fileA = "I:\WoronoraForecast\bookA.xls"
    fileB = "I:\WoronoraForecast\bookB.xls"
   
    On Error Resume Next
 '   Set wkbA = Workbooks(fileA)
    Set wkbA = ActiveWorkbook
    If Err.Number <> 0 Then 'need to open the file
        Err.Clear
     '   Set wkbA = Workbooks.Open(Filename:=fileA)
        If wkbA Is Nothing Then
            MsgBox "Could not access file: " & fileA
            Exit Sub
        End If
    End If
   
  '  Set wkbB = Workbooks(fileB)
    Set wkbB = Workbooks.Open(Filename:="I:\WoronoraForecast\bookb.xls")
    If Err.Number <> 0 Then 'need to open the file
        Err.Clear
        Set wkbB = Workbooks.Open(Filename:=fileB)
    End If
   
    If wkbB Is Nothing Then
        MsgBox "Could not access file: " & fileB
        Exit Sub
    End If
    On Error GoTo 0
   
   
    Set myName = wkbA.Names("PBI_SOTC")
       
    On Error Resume Next
    MsgBox (myName.RefersToRange)

    wkbB.Names.Add Name:=myName.Name, RefersTo:=myName.RefersToRange
    If Err.Number <> 0 Then
        MsgBox "Could not generate name: " & myName.Name & " in Workbook B." & vbCrLf & "Tried to add: " & vbCrLf & myName.Name & ": " & myName.RefersTo, vbOKOnly, "Continuing process..."
    End If
   
    'clean up, if necessary
    wkbA.Close SaveChanges:=False
    wkbB.Close SaveChanges:=True
End Sub


wkbB.Names.Add Name:=myName.Name, RefersTo:=myName.RefersToRange
BookA.xls
BookB.xls
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:sydneyguy
Comment Utility
the below snippet actualy does find the range and insert the data into the cell


wkbB.Activate
   
Range("PBI_SOTC").Select
ActiveCell.FormulaR1C1 = "8787"
0
 

Author Comment

by:sydneyguy
Comment Utility
so now i know that it will go across now i we can clena up the code to get it to roll through and create the data required and put it were it needs to go
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Ok - you can also do this more properly as:

wkbB.Range("PBI_SOTC").Value = "8787"

no need to activate/select anything.

There have been a lot of messages, so please let me know if you need further assistance and on what.

Dave
0
 

Author Comment

by:sydneyguy
Comment Utility
dave does

wkbB.Names.Add Name:=myName.Name, RefersTo:=myName.RefersToRange

actually create a new range name at the location myName.RefersToRange as given to the line?
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
It creates an exact copy of the range NAME, not the values at that range.
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
You may be wanting this instead:

wkbB.Names("PBI_SOTC").Value = wkbA.Names("PBI_SOTC").value '<- this copies the value in workbook A's PBI_SOTC to workbook B's PBI_SOTC range.

Dave
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
So, do you need me to update the code to not only copy the range names over from A to B, but the values as well?  Or what?

Dave
0
 

Author Comment

by:sydneyguy
Comment Utility
wkbB.Names.Add Name:=myName.Name, RefersTo:=myName.RefersToRange

will actually delete the old range name if it exists, but this line does not seem to than add the new one thus it goes no were so it cannot add in were it does not seem to exist or so it seems
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Actually, it just creates the name in workbook B.  If the name already exists, it overwrites it, it does not delete it.  If there is an error in the process, that could be a problem.

if you do:

loop...

debug.print myName.Name, myName.RefersToRange(external:=True)
wkbB.Names.Add Name:=myName.Name, RefersTo:=myName.RefersToRange

...

What do you get?

Dave
0
 

Author Closing Comment

by:sydneyguy
Comment Utility
thanks for you help dave in both questons
garry
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
My experience with Windows 10 over a one year period and suggestions for smooth operation
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now