Putting Sheet Tab in Order

Hi guys,

I'm quite wondering how can I put the tab in order based on my requirements. I have tried setting it in order using the Union tool but nothing is happening. It doesn't move. Is there any other way I could arrange it. Below is the current arrangement.

I would like to arrange as follows:

1. Checklist

2. Data File

3. Latest Codes Fired

4. A. Ensure LD Remediation

Please advise.

Kamen

Workflow

Reporting

Accepted answers

jdminton

no, lol. I don't know why you are seeing alphabetical, but I know that it doesn't always do alphabetical. I've run into this several times. @KamenRider try renaming the sheets to output with a,b,c,d in front of the name or 1,2,3,4 etc. and see if that resolves for you.

All comments

caltang

It is alphabetical. One way I would suggest is to have a few block-until-dones or use my 7-output-block-until-done here https://community.alteryx.com/t5/Community-Gallery/Multiple-Block-Until-Done-7-Outputs/ta-p/1259424

The first output should filter for Checklist, then second filters for Data File, and so on... so forth.

This will let you write it out in the order you desire, else it follows the alphabet.

jdminton

I see two options for this. The first is to use block until done to control how quickly the sheets finish. Alteryx adds the sheets in Excel as they finish. If you are creating a new Excel file, you will need this. I'm not sure, but testing with the small file, it kept the order after the initial file was created, but I'm not certain it will do that with a larger file, so you may need to keep the block until done tools in the workflow.

The second option is to only replace the range in the output tool. This may not be possible with your workflow though. Let me know if these work.

Order Excel Sheets.yxmd

jdminton

@caltang Excel sheet order isn't alphabetical. It is based on timing of completion.

caltang

Hmm, if that's the case then how come the user's outputs are still not sorted according to their fancy?

I agree the solution should be to use BUD to get the sheets to come out first/second/third/etc... my earlier comment is still the same solution as yours. The reason why I mentioned that is because OP's using Union tool and probably outputting based on a certain dimension as the group - in this case the 4 items in OP's post. It's sorted Alphabetically because the Union is outputting it all at once and is grouping the dimensions alphabetically.

Additional finding: https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/How-to-change-excel-sheet-order/td-p/488859

jdminton

@caltang I believe this was just a coincidence. In my example, without the block until done, I was getting them in not alphabetical order.

caltang

I did some experiment here:

The output is:

It is alphabetical for me - don't think timing plays a role here if the output is from the same spot. Because we don't declare it, the sheet names are alphabetically sorted. Don't think this is a coincidence, but I'm looking to learn if this is not the case.

Can't find much documentation on it yet...

jdminton

@caltang are you using AMP engine?

caltang

Yes - using AMP! Did you turn yours off?

jdminton

flying008

Hi, @KamenRider

If you only have 3 to 5 sheets, you might want to using the Block Until Done tool to rearrange the order of the sheets, but if you have 30 sheets or more, you can consider using BAT code in the same way as we did in the previous scenario. The only thing you have to do here is replace the SourceFile in the code with the absolute path to your file, e.g. '/123/abc.xlsx', and let the code run for the case.

@echo off &&setlocal enabledelayedexpansion &&@echo Arrange WorkSheets In ***' SourceFile '*** ... &&powershell -Command "Add-Type -AssemblyName 'Microsoft.Office.Interop.Excel'; $ID = @('1-Checklist', '2-Data File', '3-Latest Codes Fired', '4-A. Ensure LD Remediation'); $wkBook = 'SourceFile'; $objExcel = New-Object -ComObject Excel.Application; $objExcel.Visible = $false; $objExcel.DisplayAlerts = $false; $wb = $objExcel.Workbooks.Open($wkBook); foreach ($id in $ID) { $shtNum = [int]$id.Split('-',2)[0]; $shtName = $id.Split('-',2)[1]; $wb.Sheets.Item($shtName).Move($wb.Sheets.Item($shtNum)); }; $wb.Save(); $wb.Close(); $objExcel.Quit();" &&@echo *** All done ! ***

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15865

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703