Excel Reference points to original file

K

Kumar

Hello,

I have 2 worksheets in a template workbook. Cell F13 of “work sheet 2†is
referencing to G11 of “work sheet 1†using this reference(=' work sheet
1'!G11)

I am programmatically copying the worksheets from template workbook to a new
workbook and populating data into 'work sheet 1'!G11. 'work sheet 2'!F11 is
not getting the value of 'work sheet 1'!G11. Instead 'work sheet 2'!F11 is
pointing to the template workbook. This is what I see in the formula bar
(='http://localhost/mydir/templates/[My Statement.xls]work sheet 1'!G11). I
want to see =‘work sheet 1'!G11 in the formula bar and the value should get
populated from ‘work sheet 1'!G11 into ‘work sheet 2'!F13.

Please help.

Thanks in advance.
SKS
 
G

Gary Brown

Kumar,
I wrote this a while ago. It only works if both files are open. Take it
for what it's worth.
Hope it helps,
Gary Brown

'/=============================================/
Public Sub ChangeFormula2Currwkbk()
'change formulas in range from another
' workbook to current workbook
'i.e.: change =[Book2]Sheet2!A1 to
' =Sheet2!A1
'This works while both files are open. Once the
' referenced file is closed, a path is
' added to the formula.
' This is not supported in the procedure
' below.
Dim blnTF As Boolean
Dim i As Double
Dim cell As Range, rng As Range
Dim strFormula As String
Dim strAddress As String
Dim strTempFormula As String
Dim varAnswer As Variant

' On Error GoTo exit_Sub

'initialize variables
strAddress = Selection.Address
blnTF = False

'get range to be changed
Set rng = Application.InputBox( _
Prompt:= _
"Select Range of formulas to be changed.", _
Title:= _
"Delete Reference to other workbooks", _
Default:=strAddress, Type:=8)

'only look in used area of the worksheet
Set rng = Intersect(rng.Parent.UsedRange, rng)

'error checking
If rng.Count = 1 Then
varAnswer = _
MsgBox("You have only selected one cell." & _
vbCr & "Continue?", vbInformation + vbYesNo, _
"Warning...")
End If

If varAnswer = vbNo Or varAnswer = vbCancel Then
Exit Sub
End If

varAnswer = vbNo

varAnswer = _
MsgBox("'UNDO' cannot be used to change the selected " _
& "cells back to their original values." & vbCr & _
"Do you wish to continue?", vbExclamation + vbYesNo, _
"Warning...")

If varAnswer = vbNo Or varAnswer = vbCancel Then
Exit Sub
End If

'adj the selected formulas
For Each cell In rng
strTempFormula = ""
If cell.HasFormula Then
strFormula = cell.Formula
For i = 1 To Len(strFormula)
If Mid(strFormula, i, 1) = "[" Then
blnTF = True
End If
If blnTF = False Then
strTempFormula = strTempFormula & _
Mid(strFormula, i, 1)
End If
If Mid(strFormula, i, 1) = "]" Then
blnTF = False
End If
Next i
End If
cell.Formula = strTempFormula
Next cell

exit_Sub:
Set rng = Nothing

End Sub
'/==================================/


Kumar said:
Hello,

I have 2 worksheets in a template workbook. Cell F13 of “work sheet 2†is
referencing to G11 of “work sheet 1†using this reference(=' work sheet
1'!G11)

I am programmatically copying the worksheets from template workbook to a new
workbook and populating data into 'work sheet 1'!G11. 'work sheet 2'!F11 is
not getting the value of 'work sheet 1'!G11. Instead 'work sheet 2'!F11 is
pointing to the template workbook. This is what I see in the formula bar
(='http://localhost/mydir/templates/[My Statement.xls]work sheet 1'!G11). I
want to see =‘work sheet 1'!G11 in the formula bar and the value should get
populated from ‘work sheet 1'!G11 into ‘work sheet 2'!F13.

Please help.

Thanks in advance.
SKS
Click to expand...
 
G

Gary Brown

Had some time at lunch so I quickly re-wrote it. Probably made it more
complicated than necessary but, oh well.
HTH,
Gary Brown

'/=============================================/
Public Sub ChangeFormula2Currwkbk()
'change references of formulas in range from
' another workbook to current workbook
'i.e.: change ='C:\Temp\[Book2]Sheet2'!A1 to
' =Sheet2!A1
Dim blnTF_Bracket_R As Boolean
Dim blnTF_Bracket_L As Boolean
Dim blnTF_SingleQuote As Boolean
Dim i As Double
Dim cell As Range, rng As Range
Dim strFormula As String
Dim strAddress As String
Dim strTempFormula As String
Dim varAnswer As Variant

On Error GoTo exit_Sub

'initialize variables
strAddress = Selection.Address
blnTF_Bracket_R = True
blnTF_Bracket_L = True
blnTF_SingleQuote = True

'get range to be changed
Set rng = Application.InputBox( _
Prompt:= _
"Select Range of formulas to be changed.", _
Title:= _
"Delete Reference to other workbooks", _
Default:=strAddress, Type:=8)

'only look in used area of the worksheet
Set rng = Intersect(rng.Parent.UsedRange, rng)

'error checking
If rng.Count = 1 Then
varAnswer = _
MsgBox("You have only selected one cell." & _
vbCr & "Continue?", vbInformation + vbYesNo, _
"Warning...")
End If

If varAnswer = vbNo Or varAnswer = vbCancel Then
Exit Sub
End If

varAnswer = vbNo

varAnswer = _
MsgBox("'UNDO' cannot be used to change the selected " _
& "cells back to their original values." & vbCr & _
"Do you wish to continue?", vbExclamation + vbYesNo, _
"Warning...")

If varAnswer = vbNo Or varAnswer = vbCancel Then
Exit Sub
End If

'adj the selected formulas
For Each cell In rng
strTempFormula = ""
If cell.HasFormula Then
strFormula = cell.Formula
For i = Len(strFormula) To 1 Step -1
If Mid(strFormula, i, 1) = "'" Then
If blnTF_SingleQuote = False Then
blnTF_SingleQuote = True
blnTF_Bracket_L = True
End If
Else
blnTF_SingleQuote = False
End If
If Mid(strFormula, i, 1) = "]" Then
blnTF_Bracket_R = False
End If
If Mid(strFormula, i, 1) = "[" Then
blnTF_Bracket_L = False
blnTF_Bracket_R = True
End If

If blnTF_Bracket_R = True And _
blnTF_Bracket_L = True Then
strTempFormula = _
Mid(strFormula, i, 1) & _
strTempFormula
End If

Next i
End If
cell.Formula = strTempFormula
Next cell

exit_Sub:
Set rng = Nothing

End Sub
'/==================================/
 
Top