Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 523
  • Last Modified:

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

0
Mandy_
Asked:
Mandy_
  • 3
1 Solution
 
Brad GrouxCommented:
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
 
Mandy_Author Commented:
how i can insert this in my script and transfer it?
0
 
Mandy_Author Commented:
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
 
oBdACommented:
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
 
Mandy_Author Commented:
Well done. Appreciate for your assistance. Thank you very much.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now