Importing specific excel tab

1

Hi,

I’m trying to import just a single tab from a spreadsheet. The name of the tab changes every month (“July Benes”, “August Benes”, etc.), so I’m using a parameter to select the common word that does not change, i.e. “Benes”. However, my parameter to select the sheet name isn’t working. If I test it without the wildcard, it works for that single instance, but once I add the wildcard to make it select future tab names as well, it stops. What am I doing wrong?

EasyMorph version: 3.9.5.6 (4f26cb, ‘Release’)

benes

2

Hi @Yu-Gi-Oh_rulez,

Wildcard is not supported by the “Import from Excel” action. You should:

  • Use the “Spreadsheet metadata” action to create a list of sheet names
  • Filter that list for a required sheet name or names
  • Use the “Iterate” action to pass the required sheet name to a subproject (or a module starting from version 4.0) which should execute the actual import.
3

Hi @andrew.rybka,

The name of the spreadsheet changes from month to month as well. The spreadsheet metadata action does not allow me to search for a file a name keyword like I can with the import excel spreadsheet action. Do you know of a workaround for that?

Thanks!

4

@Yu-Gi-Oh_rulez, I know, but it it will require one more subproject or module. You can try to use “List of files” action, filter it’s result with an expression and the use the “Iterate” action to call your project with the “Spreadsheet metadata” action.

5

Hi @Yu-Gi-Oh_rulez

I have another suggestion. You can use Powershell to change the sheet name to a consistent name:

  1. Set up a parameter Directory = C:\Exceltest\
  2. 1st action: Run Powershell command (see code below)
  3. 2nd action: Import excel

Copy and paste this code for Powershell

Get the last file in the directory

$File = get-childitem “{Directory}” | sort LastWriteTime | select -last 1
$FilePath = “{Directory}” + “” + $File

#Open Excel
$Excel = New-Object -ComObject Excel.Application
#$FilePath = [File name with full path]
$Workbook = $Excel.Workbooks.Open($FilePath)
$Excel.Visible = $True
$Worksheet = $Workbook.Sheets.Item(1)

#Rename sheet 1
$Worksheet.Name = “Sheet1”

$Workbook.Close($True)
$excel.Quit()

The example assumes that the tab is the 1st tab in the spreadsheet.
You can adapt the code if it is not by changing .item(1).

Alternatively you can loop through all the tabs and find a the tab name and assign a parameter to it.

Hope this helps

6

See also: Multiple files, small variation in sheet name, import with parameter

7

Thank you for the suggestions, @dgudkov, @andrew.rybka, and @Rykie

Please consider adding wildcard support in the future as it’s a little more straightforward for dummies like me :slight_smile:

8

That’s an interesting suggestion, thanks.