I'm trying to write a powershell script that will loop through each excel file in the given directory, check the file for a specifically named worksheet, and then copy that file to another location if it's a match.
我正在尝试编写一个powershell脚本,该脚本将循环遍历给定目录中的每个excel文件,检查该文件是否有特定命名的工作表,然后将该文件复制到另一个匹配的位置。
Please see below for what I've already tried:
请看下面我已经尝试过的:
[void][reflection.assembly]::Loadwithpartialname("microsoft.office.excel")
$Excel = New-Object -ComObject Excel.Application
$tempLocation = "C:\Test\" # Path to read files
$files = Get-ChildItem C:\Test
ForEach ($file in $files)
{
#Check for Worksheet named TestSheet
$WorkBook = $Excel.Workbooks.Open($file)
$WorkSheets = $WorkBook.WorkSheets
foreach ($WorkSheet in $Workbook.Worksheets) {
If ($WorkSheet.Name -eq "TestSheet")
{$path = $tempLocation + "\" + $file
Write "Saving $path"
Copy-Item c:\Test\$file c:\Confirmed}
Else {Write "$path does not contain TestSheet"}
$WorkBook.Close()
}
}
This script returns no errors in PowerShell, but just sits there without writing anything or copying any files. Any ideas?
这个脚本在PowerShell中不会返回任何错误,但是只在那里不写任何东西或复制任何文件。什么好主意吗?
EDIT: Here's my final script that is now running successfully
编辑:这是我现在正在成功运行的最终脚本
$ErrorActionPreference= 'silentlycontinue'
$tempLocation = "C:\Source" # Path to read files
$targetlocation = "C:\Target"
Write "Loading Files..."
$files = Get-ChildItem C:\Source
Write "Files Loaded."
ForEach ($file in $files)
{
#Check for Worksheet named TestSheet
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $false
$Excel.DisplayAlerts = $false
$WorkBook = $Excel.Workbooks.Open($file.Fullname)
$WorkSheets = $WorkBook.WorkSheets | where {$_.name -eq "TestSheet"}
if($WorkSheets) {
$path = $tempLocation + "\" + $file
$dest = $targetlocation + "\" + $file
Write "Saving $path"
$WorkBook.SaveAs($dest)
}
$Excel.Quit()
Stop-Process -processname EXCEL
}
Read-host -prompt "The Scan has completed. Press ENTER to close..."
clear-host;
2 个解决方案
#1
4
There were several issues with my script's logic. The following script ran successfully! It took hours of research...
我的脚本逻辑有几个问题。下面的脚本运行成功!花了几个小时研究……
$ErrorActionPreference= 'silentlycontinue'
$tempLocation = "C:\Source" # Path to read files
$targetlocation = "C:\Target"
Write "Loading Files..."
$files = Get-ChildItem C:\Source
Write "Files Loaded."
ForEach ($file in $files)
{
#Check for Worksheet named TestSheet
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $false
$Excel.DisplayAlerts = $false
$WorkBook = $Excel.Workbooks.Open($file.Fullname)
$WorkSheets = $WorkBook.WorkSheets | where {$_.name -eq "TestSheet"}
if($WorkSheets) {
$path = $tempLocation + "\" + $file
$dest = $targetlocation + "\" + $file
Write "Saving $path"
$WorkBook.SaveAs($dest)
}
$Excel.Quit()
Stop-Process -processname EXCEL
}
Read-host -prompt "The Scan has completed. Press ENTER to close..."
clear-host;
#2
1
You don't need this line:
你不需要这句话:
[void][reflection.assembly]::Loadwithpartialname("microsoft.office.excel")
($Excel = New-Object -ComObject Excel.Application is sufficient here)
($Excel = New-Object -ComObject Excel。应用程序是足够的)
I don't think you're referencing the full path to your Excel files. Try modifying this line:
我认为您没有引用Excel文件的完整路径。试着修改这条线:
$WorkBook = $Excel.Workbooks.Open($file)
Amend to:
修改:
$WorkBook = $Excel.Workbooks.Open($file.Fullname)
Additionally, consider adding a filter to your Get-ChildItem command, if there are sub-directories or non-Excel files, they will cause errors:
此外,考虑在Get-ChildItem命令中添加一个过滤器,如果有子目录或非excel文件,它们将导致错误:
$files = Get-ChildItem C:\Test -filter "*.xls"
#1
4
There were several issues with my script's logic. The following script ran successfully! It took hours of research...
我的脚本逻辑有几个问题。下面的脚本运行成功!花了几个小时研究……
$ErrorActionPreference= 'silentlycontinue'
$tempLocation = "C:\Source" # Path to read files
$targetlocation = "C:\Target"
Write "Loading Files..."
$files = Get-ChildItem C:\Source
Write "Files Loaded."
ForEach ($file in $files)
{
#Check for Worksheet named TestSheet
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $false
$Excel.DisplayAlerts = $false
$WorkBook = $Excel.Workbooks.Open($file.Fullname)
$WorkSheets = $WorkBook.WorkSheets | where {$_.name -eq "TestSheet"}
if($WorkSheets) {
$path = $tempLocation + "\" + $file
$dest = $targetlocation + "\" + $file
Write "Saving $path"
$WorkBook.SaveAs($dest)
}
$Excel.Quit()
Stop-Process -processname EXCEL
}
Read-host -prompt "The Scan has completed. Press ENTER to close..."
clear-host;
#2
1
You don't need this line:
你不需要这句话:
[void][reflection.assembly]::Loadwithpartialname("microsoft.office.excel")
($Excel = New-Object -ComObject Excel.Application is sufficient here)
($Excel = New-Object -ComObject Excel。应用程序是足够的)
I don't think you're referencing the full path to your Excel files. Try modifying this line:
我认为您没有引用Excel文件的完整路径。试着修改这条线:
$WorkBook = $Excel.Workbooks.Open($file)
Amend to:
修改:
$WorkBook = $Excel.Workbooks.Open($file.Fullname)
Additionally, consider adding a filter to your Get-ChildItem command, if there are sub-directories or non-Excel files, they will cause errors:
此外,考虑在Get-ChildItem命令中添加一个过滤器,如果有子目录或非excel文件,它们将导致错误:
$files = Get-ChildItem C:\Test -filter "*.xls"