Multiple excel sheets with spaces in sheet names
- 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
I followed the instructions on the below blog and was able to get most of my sheets to pull in from my excel file. The problem is that it will not pull in my tabs that have spaces and gives me the error message listed in the attachment. Any ideas what I add to the macro to get this to work?
Solved! Go to Solution.
- Labels:
- Error Message
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi,
Your macro worked for me using with various worksheet names including/excluding spaces etc. Can you provide a sample of data from Test Database.xlsx so that I can test against the original flow?
Cheers,
Iain
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Sorry about that, attached the wrong file. Here is the excel file.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi,
Looks like the first CrossTab Tool was changing 'special' characters to underscores.
How about this as a replacement?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Can you update in my Tableau template and send back? Or provide me the formula to copy in?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
See below. The formula just replaces FileName with the text up to the delimiter '|||' (they are pipe characters even though they look like slashes). The Select changes the name back to File Name (may not be necessary depending on actions later in the flow)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I don't seem to have an option for a formula that says Split. Also will changing this still allow me to select just the sheets I want out of the database? My original excel file is larger and there is only certain sheets I want to pull in.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
As far as only allowing a subset of the sheets through, Alteryx has a very powerful filter tool that allows complex custom expressions that can be used to limit the data. Ie if there is some sort of name convention that can identify the sheets that should be used, the filter can be set up to exclude others.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
You should be able to use something like
Trim(Left([FileName],FindString([FileName], "|||")))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you so much for your help.
