Solved

Powershell insert FileOpen  dialog

Posted on 2014-09-08
5
427 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_
  • 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 83

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
A procedure for exporting installed hotfix details of remote computers using powershell
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now