Mandy_
asked on
Powershell insert FileOpen dialog
Dear experts,
could anybody help me to insert a file-Dialog for incoming files?
c:\temp\2.csv FileOpen
appreciate for your help
could anybody help me to insert a file-Dialog for incoming files?
c:\temp\2.csv FileOpen
appreciate for your help
$map=@{}
$map['C2010'] = @{}
$map['MC'] = @{}
$map['C2010']['New']=2
$map['MC']['New']=38
$map['C2010']['Delete']=60
$map['MC']['Delete']=78
$strPath="c:\temp\data.xlsx"
$objExcel = New-Object -ComObject Excel.Application
$objExcel.Visible=$false
$WorkBook = $objExcel.Workbooks.Open($strPath)
$worksheet = $workbook.sheets.item("MA")
Import-Csv c:\temp\2.csv -Delimiter ';'| %{
$ver = $_.Version
if(!($ver -eq '')){
$action = $_.action
$rowNum = $map[$ver][$action]
$col=1
$_.PSObject.Properties | %{
$worksheet.cells.item($rowNum,$col++) = $_.value
}
$map[$ver][$action] = $rowNum+1
}
}
$worksheet.UsedRange.Rows | select -Skip 1 | ? { $_.Cells.Item(1).Value2 } |
% {
$actRow = $_.Cells
$actRow.Item(2).Value2.ToLower() -match "^(?<last>[\w\s-]+),( (?<middle>[\w]+))? (?<first>[\w-]+)( (?<code>[\w()]+))?$" | Out-Null
$first, $middle, $middle2, $last = $matches["first", "middle", "middle2", "last"] -replace "[\W]","."
$code = $matches["code"] -replace "[()]"
if ($middle) { $middle = "." + $middle }
if ($code) { $code = "." + $code }
$actRow.Item(13).Formula = "= ""{0}{1}{2}.{3}{4}@"" & SVERWEIS(C{5}; Company!A:B; 2; falsch)" -f $first,$middle,$middle2,$last,$code,$_.Row
$actRow.Item(13).Formula = $_.Cells.Item(13).Value2
}
$WorkBook.Save()
$objExcel.Quit()
ASKER
how i can insert this in my script and transfer it?
ASKER
hi folks,
i tried this below but still not working. Could anybody correct it?
Thanks in advance.
i tried this below but still not working. Could anybody correct it?
Thanks in advance.
[System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") | Out-Null;
#File dialog box to browse to file.
function diagbx()
{
$OpenFileDialog = New-Object System.Windows.Forms.OpenFileDialog
$OpenFileDialog.initialDirectory = $initialDirectory
$OpenFileDialog.filter = "Comma Separated Value (*.csv)|*.csv
$OpenFileDialog.ShowDialog()
$OpenFileDialog.filename
}
#get the results of the function
$file = diagbx;
#Check to see if diag was cancled.
if ($file[0] -eq "Ok")
{
#get the content of the file
Import-Csv $file[1] -Delimiter ';'|
%{$map=@{}
$map['C2010'] = @{}
$map['MC'] = @{}
$map['C2010']['New']=2
$map['MC']['New']=38
$map['C2010']['Delete']=60
$map['MC']['Delete']=78
$strPath="c:\temp\data.xlsx"
$objExcel = New-Object -ComObject Excel.Application
$objExcel.Visible=$false
$WorkBook = $objExcel.Workbooks.Open($strPath)
$worksheet = $workbook.sheets.item("MA")
$ver = $_.Version
if(!($ver -eq '')){
$action = $_.action
$rowNum = $map[$ver][$action]
$col=1
$_.PSObject.Properties | %{
$worksheet.cells.item($rowNum,$col++) = $_.value
}
$map[$ver][$action] = $rowNum+1
}
}
$worksheet.UsedRange.Rows | select -Skip 1 | ? { $_.Cells.Item(1).Value2 } |
% {
$actRow = $_.Cells
$actRow.Item(2).Value2.ToLower() -match "^(?<last>[\w\s-]+),( (?<middle>[\w]+))? (?<first>[\w-]+)( (?<code>[\w()]+))?$" | Out-Null
$first, $middle, $middle2, $last = $matches["first", "middle", "middle2", "last"] -replace "[\W]","."
$code = $matches["code"] -replace "[()]"
if ($middle) { $middle = "." + $middle }
if ($code) { $code = "." + $code }
$actRow.Item(13).Formula = "= ""{0}{1}{2}.{3}{4}@"" & SVERWEIS(C{5}; Company!A:B; 2; falsch)" -f $first,$middle,$middle2,$last,$code,$_.Row
$actRow.Item(13).Formula = $_.Cells.Item(13).Value2
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Well done. Appreciate for your assistance. Thank you very much.
Open in new window
More info - http://blogs.technet.com/b/heyscriptingguy/archive/2013/01/22/use-a-net-framework-object-to-open-a-file-dialog-box.aspx