查找用于Excel功能的Powershell名称 [英] Finding Powershell names for Excel features

问题描述

我想自动化Excel流程的数据→导入文本文件文本导入向导

I want to automate the Excel process of Data → Import Text File → text import wizard

如何找到这些菜单的PowerShell名称？

How do I find the PowerShell name for these menus?

我的代码我想让它在一个文件夹（有分隔符）中找到.txt文件，并将它们导入到单独的Excel文件中。

Here is my code. I want it to find .txt files in a folder (which have delimiters) and import them into separate Excel files.

#needs 2个或更多文件可以正常工作。 rubbish.txt必须与其他文件夹在一起。

#needs 2 or more files to work. rubbish.txt must be in folder with other files.

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$excel.DisplayAlerts = $false
$path = "S:\DATA EXCHANGE\Testing" #where the original files are
$path2="S:\DATA EXCHANGE\Resurgent\fromSFG\test" #where you want the new files to go
 Set-Location $path

$a= Get-ChildItem $path -recurse -include *.txt #only finds the text files
$numOfFiles= $a.count


For ($n=$numOfFiles-1; $n -gt -1; $n--)
{$b=$a[$n].name ;


    $z=$path+"/"+$b #finds original file for import

        $wb = $excel.Workbooks.OpenText(   #the import
        $z, # file to open
        [Microsoft.Office.Interop.Excel.XlPlatform]::xlWindows, 
         1, # start from row 1
         [Microsoft.Office.Interop.Excel.XlTextParsingType]::xlDelimited,
         [Microsoft.Office.Interop.Excel.XlTextQualifier]::xlTextQualifierDoubleQuote,
         $false, # Consecutive Delimiter
        $true, # tab
         $false, # semicolon
         $true, # comma
         $false, # space
         $true,  # use other
         '|')


 $y= $b -replace ".txt.*" #gets filename without extension

 $y= $path2+"/"+$y #adds path to filename
 #$y= $y+".xlsx" #adds xlsx to filename

  if ($b -eq "rubbish.txt")  {$Excel.ActiveWorkbook.Close()}


 $Excel.ActiveWorkbook.SaveAs("$y")
 #$Excel.ActiveWorkbook.Close()

}



#$Excel.Workbooks.Close()
#$Excel.Quit()

推荐答案

如果我理解你的问题，你正在寻找一种方法来导入CSV文件直接进入Excel。

If I understood your question, you are looking at a way to import a CSV file directly into Excel.

我使用$ excel.workbooks对象的 Open（）方法。 / p>

I use the Open() method of the $excel.workbooks object.

$excel = new-object -comobject excel.application
$file = get-item "d:\scripts\test.csv"
$excel.Visible=$true
$excel.displayalerts = $False
$wb = $excel.workbooks.open($file)

如果这不起作用，我使用文件将.CSV文件重命名为.TXT，并使用 OpenText

If that doesn't work, I use files I rename the .CSV file to .TXT, and open them with OpenText

UPDATED使用'|'作为分隔符

$wb = $excel.Workbooks.OpenText(
         "mycsv.txt", # file to open
         [Microsoft.Office.Interop.Excel.XlPlatform]::xlWindows, 
         1, # start from row 1
         [Microsoft.Office.Interop.Excel.XlTextParsingType]::xlDelimited,
         [Microsoft.Office.Interop.Excel.XlTextQualifier]::xlTextQualifierDoubleQuote,
         $false, # Consecutive Delimiter
         $false, # tab
         $false, # semicolon
         $false, # comma
         $false, # space
         $true,  # use other
         '|')

不要直接使用.CSV文件使用 OpenText ，重新命名他们。 Excel似乎与CSV命名文件不同。

Don't use OpenText with .CSV files directly, rename them first. Excel seems to work with CSV named files differently.

这篇关于查找用于Excel功能的Powershell名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！