Link to home
Start Free TrialLog in
Avatar of Mandy_
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

$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()

Open in new window

Avatar of Brad Groux
Brad Groux
Flag of United States of America image

Here are a couple of options -

Add-Type -AssemblyName System.windows.forms | Out-Null

        $OpenFileDialog = New-Object System.Windows.Forms.OpenFileDialog
        $OpenFileDialog.Multiselect = $True
        $OpenFileDialog.Filter = "All files (*.*)| *.*"
        # important if show help is not set to true the display of
        # the dialog may fail. The dialog is displayed in the ISE 
        # but when running from command line it never pops up
        $OpenFileDialog.showHelp = $true
        $OpenFileDialog.ShowDialog() | Out-Null

        foreach($fullFilePath in $OpenFileDialog.FileNames)
        {
            $fullFilePath
            #or do something usefull               
        }

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
Avatar of Mandy_
Mandy_

ASKER

how i can insert this in my script and transfer it?
Avatar of Mandy_

ASKER

hi folks,
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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of oBdA
oBdA

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mandy_

ASKER

Well done. Appreciate for your assistance. Thank you very much.