Sleeper: Cell References & Links - Please help

[qwaz]

Hey everyone!


I don?t know if this is possible but this is what I would like to do:


When I click on a cell in a particular column I would like to be redirected to a specific .xls source file. The source file name would based on the contents of cell A in the same row (Example: A1=PROJ001 --> opens PROJ001.xls). The cell that?s been clicked on would display the contents of one cell from the source file.
However, ff the file cannot be found, a template file is opened and saved under the name displayed in cell (in this case PROJ001.xls)

I understand that the structure should look like this but I don?t know how to code it. Please help.


Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Address = "B1:B10" Then
?--> CODE
End If
End Sub

Thanks a bunch!

qwaz

Running: Excel 2003

[Killian]

Hi qwaz and welcome to VBAX :hi:

I tidied up your post a little - hope that's ok

Regarding the solution, here's some code that does the job though I'm not clear what you want to do when a new file is created... (maybe add the filename to the cell value???)
For the sake of clarity, I've used the FileSystemObject to check for the file. You may prefer to use another method (like Excel's FileSearch object) once you get this working how you want it


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'set string constants for source file path and template name
Const SOURCE_FILE_PATH As String = "C:\Documents and Settings\Killian\Desktop\VBAX\"
Const SOURCE_TEMPLATE_NAME As String = "SourceTemplate.xlt"
Dim fso
Dim strFile As String
Dim wbSource As Workbook
'only process single cell selections in column B that have a value
If Not IsEmpty(Target.Value) And Target.Column = 2 And Target.Cells.Count = 1 Then
Application.ScreenUpdating = False
Set fso = CreateObject("Scripting.FileSystemObject")
strFile = SOURCE_FILE_PATH & Target.Value & ".xls"
If fso.FileExists(strFile) Then 'file exists - open it
Set wbSource = Workbooks.Open(strFile)
'assuming we're dealing with sheet 1 (?)
Target.Value = wbSource.Sheets(1).Cells(Target.Row, 1).Value
wbSource.Close False 'close without saving
Else 'file doesn't exist - create a new one
Set wbSource = Workbooks.Add(SOURCE_FILE_PATH & SOURCE_TEMPLATE_NAME)
wbSource.SaveAs strFile 'save new file
wbSource.Close False 'close without saving
MsgBox "New file '" & strFile & "' created."
End If
Set wbSource = Nothing
Set fso = Nothing
Application.ScreenUpdating = True
End If
End Sub

[Bob Phillips]

Might also be worth testing that file is not already open, and you need to pick up column A according to the spec, not target column


Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'set string constants for source file path and template name
Const SOURCE_FILE_PATH As String = "C:\Documents and Settings\Killian\Desktop\VBAX\"
Const SOURCE_TEMPLATE_NAME As String = "SourceTemplate.xlt"
Dim fso As Object
Dim strFile As String
Dim wbSource As Workbook
Application.ScreenUpdating = False
'only process single cell selections that have a value
With Target
If Not IsEmpty(Me.Cells(.Row, 1).Value) And .Cells.Rows.Count = 1 Then
Set fso = CreateObject("Scripting.FileSystemObject")
strFile = SOURCE_FILE_PATH & Target.Value & ".xls"
If IsWorkbookOpen(Me.Cells(.Row, 1).Value) Then
'assuming we're dealing with sheet 1 (?)
.Value = Workbooks(Me.Cells(.Row, 1).Value).Sheets(1).Cells(.Row, 1).Value
'don't close if already open
ElseIf fso.FileExists(strFile) Then 'file exists - open it
Set wbSource = Workbooks.Open(strFile)
'assuming we're dealing with sheet 1 (?)
.Value = wbSource.Sheets(1).Cells(.Row, 1).Value
wbSource.Close False 'close without saving
Else 'file doesn't exist - create a new one
Set wbSource = Workbooks.Add(SOURCE_FILE_PATH & SOURCE_TEMPLATE_NAME)
wbSource.SaveAs strFile 'save new file
wbSource.Close False 'close without saving
MsgBox "New file '" & strFile & "' created."
End If
Set wbSource = Nothing
Set fso = Nothing
End If
End With
Application.ScreenUpdating = True
End Sub

Function IsWorkbookOpen(WBName As String) As Boolean
On Error Resume Next
IsWorkbookOpen = CBool(Len(Workbooks(WBName).Name))
End Function

[Killian]

Might also be worth testing that file is not already open Good point
and you need to pick up column A according to the spec, not target columnthough my interpretation of the spec was the "particular column" was the Target.Address = "B1:B10" referred to in the code - hence screening out all but colB.

[Bob Phillips]

Good pointthough my interpretation of the spec was the "particular column" was the Target.Address = "B1:B10" referred to in the code - hence screening out all but colB.

Uum! I think the references to rows and columns got a bit confused, but I gave most weight (maybe wrongly) to the statement '... name would based on the contents of cell A in the same row ...'.

qwaz, enlighten us.

[qwaz]

Thank you sooo much Killian & xld!
That’s exactly what I had in mind.

For the sake of clarifying things... I wanted an excel file (file name from colA) to load when I click on the respective colB cell. I made a few small modifications and I got it to work as I wanted.

There’s just one more thing id like to ask you. When the source file is opened and modifications are made in the source file, the destination file is not update automatically. I have to close the file, save the changes, click again on a cell in colB and open the file and then close it to see the changes in the respective cell. In other words, the destination file only updates when no changes are made in the source file. My question is: Is there a better way to link the source and destination cells so that the destination cell is updated whenever modifications are made in the source file?

Thanks a bunch for your help! qwaz

[qwaz]

bump :)

[qwaz]

Hi again!

Ive got an idea and I would like to hear your opinion.

Instead of just copying the value from one workbook to another, I figured it would probably be better to insert a reference to the other workbook upon the creaton of the file. So something like this:


...
Else 'file doesn't exist - create a new one
Set wbSource = Workbooks.Add(SOURCE_FILE_PATH & SOURCE_TEMPLATE_NAME)
wbSource.SaveAs strFile 'save new file
MsgBox "New file '" & strFile & "' created."
.Formula = "'" & FileName & "'!" & "A" & y
End If
...




Is this the right way to call a reference? :think: (sorry im a newb)

I'll try it tomorrow and I'll let you know how it goes.
Thanks a bunch. Your help is greatly appreciated.

q

[Bob Phillips]

Is this the right way to call a reference? :think: (sorry im a newb)

I'll try it tomorrow and I'll let you know how it goes.
Thanks a bunch. Your help is greatly appreciated.

A refreence to a file is of the form


=[cracker.xls]Sheet1!$D$35

which changes to


='G:\Development\excel\[cracker.xls]Sheet1'!$D$35

when the other workbook is closed, so you need to set your formula accordingly.