Use external file variable in formula

[Steve]

Hi all. I have the below code to open the dialogue box to prompt the user to open a data file (a .csv file with only one sheet - Sheet1). I think I have that file path read into a variable. Now I need to write a formula into the original file against the data in the newly opened data file. The data file path and name will change, so it needs to be variable based on thefile the user selects. How can I incorporate the path of the data file into the formula in the master file? Thanks!

Sub Main()

Dim Filter As String, Title As String
Dim FilterIndex As Integer
Dim Source As Variant

Set Master = ThisWorkbook

Filter = "Excel Files (*.xls),*.xls," & _
"Text Files (*.txt),*.txt," & _
"All Files (*.*),*.*"

FilterIndex = 3

Title = "Select the Data Source File"

ChDrive ("C")
ChDir ("C:\")

With Application
Source = .GetOpenFilename(Filter, FilterIndex, Title)
' Reset Start Drive/Path
ChDrive (Left(.DefaultFilePath, 1))
ChDir (.DefaultFilePath)
End With

If Source = False Then
Exit Sub
End If

Workbooks.Open Source

Ticket.Activate

Set frng = Range("A1")
With frng
'--------------------------------------------------------------------------
'THIS IS THE PART I CANE SEEM TO FIGURE OUT
..Formula = "='" & Source & "Sheet1!B5"
'--------------------------------------------------------------------------
End With

 

[Jim Cone]

You are missing a single apostrophe and the workbook name must be enclosed in brackets.
'---
Dim strName As String
Source = Application.GetOpenFilename()
Workbooks.Open Source
strName = Dir(Source)
Ticket.Activate
Range("A1").Formula = "='[" & strName & "]Sheet1'!B5"
'---
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(List Files XL add-in: finds and lists files/folders with hyperlinks)







"Steve" <[email protected]>
wrote in message
Hi all. I have the below code to open the dialogue box to prompt the user to open a data file (a
..csv file with only one sheet - Sheet1). I think I have that file path read into a variable. Now I
need to write a formula into the original file against the data in the newly opened data file. The
data file path and name will change, so it needs to be variable based on the file the user selects.
How can I incorporate the path of the data file into the formula in the master file? Thanks!

Sub Main()
Dim Filter As String, Title As String
Dim FilterIndex As Integer
Dim Source As Variant

Set Master = ThisWorkbook
Filter = "Excel Files (*.xls),*.xls," & _
"Text Files (*.txt),*.txt," & _
"All Files (*.*),*.*"
FilterIndex = 3
Title = "Select the Data Source File"
ChDrive ("C")
ChDir ("C:\")
With Application
Source = .GetOpenFilename(Filter, FilterIndex, Title)
' Reset Start Drive/Path
ChDrive (Left(.DefaultFilePath, 1))
ChDir (.DefaultFilePath)
End With
If Source = False Then
Exit Sub
End If
Workbooks.Open Source
Ticket.Activate
Set frng = Range("A1")
With frng
'--------------------------------------------------------------------------
'THIS IS THE PART I CAN'T SEEM TO FIGURE OUT
..Formula = "='" & Source & "Sheet1!B5"
'--------------------------------------------------------------------------
End With

 

[Steve]

Awesome! Thanks Jim!