Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Range().Formula with a Filename & Path
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Range().Formula with a Filename & Path
BJTex wrote:
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do i display the Filename without the path? | Excel Discussion (Misc queries) | |||
Pulling in the path to a filename | Excel Worksheet Functions | |||
Are there template footers for "Filename and Path" in Excel. How? | Excel Discussion (Misc queries) | |||
Excel 2K: Putting Filename & path in footer | Excel Discussion (Misc queries) | |||
Footer - FileName and Path | Excel Discussion (Misc queries) |