Using Range().Formula with a Filename & Path

[Guest]

I'm apparently missing the proper syntax or method of using a string in
formulas that the VB code pastes in one spreadsheet
([WorkBook2.xls]CombList'!$E7 through $E25 one at a time) referencing values
in the corresponding cell location in another spreadsheet
(WorkBook1.xls]CombList'!$E7 through $E25 one at a time).

Each workday a new WorkBook is created (e.g. WorkBook2.xls) that has
formulas comparing E7 in today's WorkBook to the same cells on the prior
workday's values (e.g. WorkBook1.xls). For example, I want to use VB to
paste a formula in F7 showing the prior workday's value from E7 (e.g. a
formula that sets ([WorkBook2.xls]CombList'!$F7 equal to
WorkBook1.xls]CombList'!$E7). The filenames and folder locations can vary
depending on not only the workday but also which project is being tracked.

My code excerpt is shown below.
It bombs off at the statement: Range("F7").Formula = FormulaString

Any help for this novice VB programmer?


Dim LinkFN As String
Dim FormulaString As String

' Get path and filename to the file that you want to compare this Hit List
to
LinkFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls),
*.xls", Title:="Please select the Excel file that you want to compare today's
List against.")
If LinkFN = "False" Then
MsgBox "No file selected. You must select a file"
Exit Sub
End If

FormulaString = "=" & LinkFN & "CombList'!E7"
Range("F7").Formula = FormulaString

 

[Franz Verga]

I'm apparently missing the proper syntax or method of using a string
in formulas that the VB code pastes in one spreadsheet
([WorkBook2.xls]CombList'!$E7 through $E25 one at a time) referencing
values in the corresponding cell location in another spreadsheet
(WorkBook1.xls]CombList'!$E7 through $E25 one at a time).

Each workday a new WorkBook is created (e.g. WorkBook2.xls) that has
formulas comparing E7 in today's WorkBook to the same cells on the
prior workday's values (e.g. WorkBook1.xls). For example, I want to
use VB to paste a formula in F7 showing the prior workday's value
from E7 (e.g. a formula that sets ([WorkBook2.xls]CombList'!$F7 equal
to WorkBook1.xls]CombList'!$E7). The filenames and folder locations
can vary depending on not only the workday but also which project is
being tracked.

My code excerpt is shown below.
It bombs off at the statement: Range("F7").Formula = FormulaString

Any help for this novice VB programmer?


Dim LinkFN As String
Dim FormulaString As String

' Get path and filename to the file that you want to compare this
Hit List to
LinkFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls),
*.xls", Title:="Please select the Excel file that you want to compare
today's List against.")
If LinkFN = "False" Then
MsgBox "No file selected. You must select a file"
Exit Sub
End If

FormulaString = "=" & LinkFN & "CombList'!E7"
Range("F7").Formula = FormulaString

Try to modify in this way:

FormulaString = "='[" & LinkFN & "]CombList'!E7"

and I think you can also use (but I'm not sure...)

Range("F7").Value instead of Range("F7").Formula



--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy