Solved

Powershell insert FileOpen  dialog

Posted on 2014-09-08
5
454 Views
Last Modified: 2014-09-09
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

0
Comment
Question by:Mandy_
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 14

Expert Comment

by:Brad Groux
ID: 40310085
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
0
 
LVL 2

Author Comment

by:Mandy_
ID: 40310188
how i can insert this in my script and transfer it?
0
 
LVL 2

Author Comment

by:Mandy_
ID: 40310488
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

0
 
LVL 84

Accepted Solution

by:
oBdA earned 500 total points
ID: 40311412
Your "diagbx" function returns a string, so the "$file[0]" you're using in line 16 will never be "Ok", but the first character of the file path selected (so probably "C"). Accordingly, the $file[1] you're trying to open in line 19 will be ":".
Try it with this:
Function Get-FormsOpenFileDialog([string]$InitialDirectory, [string]$Filter = 'All files (*.*)|*.*', [uint32]$FilterIndex = 1) {
	[System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms") | Out-Null
	If ([string]::IsNullOrEmpty($InitialDirectory) -Or (-Not (Test-Path -Path $InitialDirectory))) {
		$InitialDirectory = $ENV:UserProfile
	}
	$OpenFileDialog = New-Object -TypeName System.Windows.Forms.OpenFileDialog
	$OpenFileDialog.InitialDirectory = $InitialDirectory
	$OpenFileDialog.Filter = $Filter
	$OpenFileDialog.FilterIndex = $FilterIndex
	$Result = $OpenFileDialog.ShowDialog()
	If ($Result -eq [System.Windows.Forms.DialogResult]::OK) {
		Return $OpenFileDialog.FileName
	}
}

$map = @{}
$map['C2010'] = @{}
$map['MC'] = @{}
$map['C2010']['New'] = 2
$map['MC']['New'] = 38
$map['C2010']['Delete'] = 60
$map['MC']['Delete'] = 78

$strXlsFile = "C:\temp\data.xlsx"
$strCsvFile = Get-FormsOpenFileDialog -InitialDirectory "C:\Temp" -Filter "Comma separated values file (*.csv)|*.csv"
If (-Not (Test-Path -Path $strXlsFile)) {
	"Script canceled, xls file '$strXlsFile' not found!" | Write-Host -ForegroundColor Red
	Exit 1
}
If (-Not (Test-Path -Path $strCsvFile)) {
	"Script canceled, no csv file selected!" | Write-Host -ForegroundColor Red
	Exit 1
}

$objExcel = New-Object -ComObject Excel.Application
$objExcel.Visible = $false
$WorkBook = $objExcel.Workbooks.Open($strXlsFile)
$worksheet = $workbook.sheets.item("MA")

Import-Csv -Path $strCsvFile -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

0
 
LVL 2

Author Closing Comment

by:Mandy_
ID: 40311460
Well done. Appreciate for your assistance. Thank you very much.
0

Featured Post

Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
In previous parts of this Nano Server deployment series, we learned how to create, deploy and configure Nano Server as a Hyper-V host. In this part, we will look for a clustering option. We will create a Hyper-V cluster of 3 Nano Server host nodes w…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question