PowerShell-Excel - Importing sheets

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

ryjones

Thread Starter
Joined
Jul 5, 2016
Messages
1
Hello-

This is my first post here so apologies if I'm off on how to submit. I'm a newbie to PowerShell and was handed a script someone else wrote to now run on my own. The problem is, the data I'm getting is getting inconsistent so rather than rely on them to make it so or manually alter it, I'm hoping I can use it as is by updating my script.

I have a script which looks at the first tab of multiple excel sheets and imports only the first sheet of each workbook into a new one. The tab that is copied is renamed to the name of the original workbook it came from. My issue is that the name of the first worksheet is not always consistent. I believe the issue is stemming from this part of the code:

$fullName = $item.FullName

$fee = $excel.Workbooks.Open("$fullName")

$feeSchedule = $fee.worksheets.Item(1)

$feeSchedule.Copy($mv, $compareCombo.Sheets.Item($i)) | Out-Null


$feeSheet = $compareCombo.worksheets.Item("Sheet1 (2)")

$name = $item.BaseName.Split(" ")[0]

$feeSheet.Name = $name


$i++

I tried renaming the ("Sheet1 (2)" to “$page1 + ‘(2)’” but that threw off what the sheets were named and gave me errors as well. Any help would be appreciated.

-Ry
 
Joined
Jun 8, 2001
Messages
2,583
Welcome to TSG,
I don't have much to offer, checking my PShell stuff and its all manipulating CSV files into XML. My thought, there is nothing wrong with the PowerShell and the blame lies with who/whatever is creating the input. If they want you to process the file they need to conform to the standards.

You could write a work around code for different tab names for ever. I understand your desire to be accommodating but in the data world there is nothing wrong with requiring the input data be 100% correct.

Check this at StackOverFlow for clean way to loop thru the tab names.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top