Interface Tools: Update Value with Formula Issue
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi All,
- FileName: C:\Users\zszykowny006\Documents\Wiffle Ball\2018\2018 Bats for Brains Budget.xlsx|||`2018 Budget$`
- FileName1: C:\Users\zszykowny006\Documents\Wiffle Ball\2018\2018 Bats for Brains Budget.xlsx`
- FileName2: ||List of Sheet Names>
Macro Workflow
- Currently My Macro Tool Looks like this:
- The First Action Tool is "Update Value (Default)"
- I select the box "Replace a specific string" and then remove the "||List of Sheet Names>" so all I have remaining is the Filename1 value listed previously.
- The Second Action Tool is "Update Value (Default)"
- I select the box "Replace a specific string" and then remove the "C:\Users\zszykowny006\Documents\Wiffle Ball\2018\2018 Bats for Brains Budget.xlsx||" & " S' " so all I have remaining is the First Tab Name.
- This will have same Tab name as the Tab selected in the Input File Listed below.
- Input File: Choose the First Tab of the excel file
- (It can be any tab, but easiest to just choose the First. Also, this Tab name will be the one listed in the 2nd action tool previously mentioned)
- First Action Tool:
- Substring([#1],0,Length(Left([#1],FindString([#1], "|"))))
- This should get me the result:
- C:\Users\zszykowny006\Documents\Wiffle Ball\2018\2018 Bats for Brains Budget.xlsx
- Second Action Tool:
- Substring([#1],Length(Left([#1],FindString([#1],"|")+1)),
(Length(Left([#1], FindString([#1], "$")))
-Length(Left([#1],FindString([#1],"|")+1)))) - This should get me the name of my first tab:
- 2018 Budget
- Error: New MULTIPLE TABS BATCH MACRO_V2 (79): Record #1: Tool #11: File not found "C:\Users\zszykowny006\Documents\2018 Budget"
I have confidence these Formulas above are correct after testing them in a different workflow using the formula tool.
- Why does the full file name not come through? The problem seems to be with my First Action Tool formula...As it only only pulls through " C:\Users\zszykowny006\Documents\" and is missing "Wiffle Ball\2018\2018 Bats for Brains Budget.xlsx"
- If the full file name comes through, I feel like this should work the same way as when I was manually manipulating the Update Value (Default) option.
I have spent many a hour on this, so any help would be truly appreciated!
Thanks!
Solved! Go to Solution.
- Labels:
- Batch Macro
- Error Message
- Workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@ripcityscorp15 I will have a look at why your version is not working. In the meantime, I've attached the way I would do it using reference shortcuts. I find these much easier to work with.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@ripcityscorp15 You have 2 action tools, and the second one is completely overwriting everything that the first one is doing. I would connect both of your control parameters to 1 action tool. Perhaps I'm missing something, but this simpler formula seemed to work with your dataset (where #1 is the file path and #2 is the sheet name):
For future reference, you can use the field [Destination] in the formula to use what's already in the tool. The action tool is the 1 case where you can use data coming in as well as the data that's already in your tool to build your formula. In this particular case, i think it's easiest to just use 1 action tool. For an example, there are times where I use a function like:
Replace([Destination],"text to replace", DateTimeParse([#1],"%m/%d/%Y"))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Incredibly helpful! There was very little, via search, I could find in the Community, giving good detail about this specific functionality of the Action Tool. Thank you for taking the time to explain it all out!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Great tip!!! Thanks a lot @patrick_digan, this has helped me fix a similar issue.
Cheers!
