Solved

Powershell insert FileOpen  dialog

Posted on 2014-09-08
5
430 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

How to sign a powershell script so you can prevent tampering, and only allow users to run authorised Powershell scripts
This script can help you clean up your user profile database by comparing profiles to Active Directory users in a particular OU, and removing the profiles that don't match.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

920 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

17 Experts available now in Live!

Get 1:1 Help Now