This code works, but could it all be done in VBA?

[Razzy]

Sub rabbit()
Dim myFile As String
Dim FilePicker As FileDialog

Dim B       As String
Dim B1()    As String

Set FilePicker = Application.FileDialog(msoFileDialogFilePicker)
    With FilePicker
      .Title = "Choose file"
      .AllowMultiSelect = False
        If .Show <> -1 Then GoTo Velg
        myFile = .SelectedItems(1)
    End With
    
Workbooks.Open Filename:=myFile
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "=CELL(""filename"")"
    B = Range("A1").Value
    Selection.ClearContents
    B = "='" & B & "'" & "!$A$1"
    B1 = Split(B, "]")
    B = B1(0) & "]"

***Any better idea how to get the workbook reference? Can I avvoid writing inside the workbook?

The code will turn a filepath into a workbook reference.

 

[offthelip]

is this what you need, it uses the FSO object,

Sub rabbit()
Dim FSO
Set FSO = CreateObject("Scripting.FileSystemObject")

Dim myFile As String
Dim FilePicker As FileDialog

Dim B       As String
Dim B1()    As String

Set FilePicker = Application.FileDialog(msoFileDialogFilePicker)
    With FilePicker
      .Title = "Choose file"
      .AllowMultiSelect = False
        If .Show <> -1 Then Exit Sub
        myFile = .SelectedItems(1)
        fname = FSO.GetFileName(myFile) 'The file name
       MsgBox (fname)
    End With
End Sub

 

[Razzy]

This outputs "File 1.xlsx"

But I need: C:\Users\Sylvester\Desktop\[File 1.xlsx]. To further turn it into a workbook reference.

But it is okay. Thx for your help! I can use what I have.

Right now I am struggeling with replacing the workbook reference Ive created, with a text I have in my template "rabbit.xlsm":

Workbooks("rabbit.xlsm").Activate
Sheets("Oversikt").Select
    Cells.Select
    Selection.Replace What:="FILE1", Replacement:=B, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

Is it not possible to search and replace a line of text in a template, with a variable B?

 

[Razzy]

Variabe B is: ='C:\Users\Sylvester\Desktop\[File 1.xlsx], and in my template I write: FILE1Sheet4'!$A$4, wich should output:

='C:\Users\Sylvester\Desktop\[File 1.xlsx]!$A$4

Right now it wont replace. It works with text.

 

[Razzy]

I will simplyfi in a new post