excel,powershell,csv,export-to-csv,powershell-v4.0 , Powershell script for Excel error code

Powershell script for Excel error code


Tag: excel,powershell,csv,export-to-csv,powershell-v4.0

I'm trying to convert an Excel .xls file that has several worksheets into a .csv with Powershell 4.0. I know the SaveAs in the for each loop isn't phrased right, and that the error is pointing to line 17 and character 9, I just don't know how to fix it or how to interpret the error code 0x800A03EC.

Here's the script:

Function ExportWSToCSV ($inputWorkbookPath, $outputFilePrefix, $outputDirectory)

    #Start Excel invisibly without pop-up alerts.
    $inputWorkbookPath = "R:\Unclaimed Property\NC State\Jun 2015\" + `
                         "NC_RAW_JUL1986thruMAR2013" + ".xls"

    $excel = New-Object -ComObject Excel.Application
    $excel.Visible = $false
    $excel.DisplayAlerts = $false

    #Open Excel file.
    $workBook = $excel.Workbooks.Open($inputWorkbookPath) 

    foreach ($workSheet in $workBook.Worksheets)
        $n = $inputWorkbookPath + "_" + $workSheet.Name
        $workSheet.SaveAs($outputDirectory + $n + ".csv", 6)

ExportWSToCSV -inputWorkbookPath "R:\Unclaimed Property\NC State\Jun 2015\NC_RAW_JUL1986thruMAR2013.xls" `
              -outputFilePrefix "output_" `
              -outputDirectory "R:\Unclaimed Property\NC State\Jun 2015\"

Here's the error:

Exception calling "SaveAs" with "2" argument(s): "Exception from    HRESULT: 0x800A03EC"
At \\ncdfs1\documents$\ANDREWN\My Documents\PSscript_for_NC.ps1:17   char:9
+         $workSheet.SaveAs($outputDirectory + $n + ".csv", 6)
+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : NotSpecified: (:) [],  MethodInvocationException
+ FullyQualifiedErrorId : ComMethodTargetInvocation


I strongly feel your issue is coming from your path concatenation logic. Lets look at the following code from within your loop.

$n = $inputWorkbookPath + "_" + $workSheet.Name
$workSheet.SaveAs($outputDirectory + $n + ".csv", 6)

In your example call your variables I think are mapped as follows:

$inputWorkbookPath equals "R:\Unclaimed Property\NC State\Jun 2015\NC_RAW_JUL1986thruMAR2013.xls"
$workSheet.Name equals "Bagel" # I made that up.
$outputDirectory equals "R:\Unclaimed Property\NC State\Jun 2015\"

So you are going to try and set the new file name as:

R:\Unclaimed Property\NC State\Jun 2015\R:\Unclaimed Property\NC State\Jun 2015\NC_RAW_JUL1986thruMAR2013.xls_Bagel.csv

Which does not look right at all. If you just had the line in your loop

$outputDirectory + $n + ".csv"

I think you would see the issue. Just some simple debugging.

Lets fix this

First guess is that you just need to change it to something like this

$path = $outputDirectory + $workSheet.Name + ".csv"
$workSheet.SaveAs($path, 6)

Outside the scope of this question it would be a good idea to check if that path exists before saving it. It would save some potential headache.


Remove all folders .old

I'm trying to delete all folders in \\kiewitplaza\vdi\Appsense_profiles that end with .old. The piece I have that says Write-Host $name is just me trying to test before I delete anything. $name = Get-ChildItem "\\kiewitplaza\vdi\Appsense_profiles" if ($name.name.EndsWith(".old")) { Write-Host $name #Remove-Item "\\kiewitplaza\vdi\Appsense_profiles\$name.old" } ...

VBA - Unable to pass value from Private to Public Sub

I have a tool which I am designing to present a number of questions to a user in a set of userforms. The form will generate a score via passing an integer result from the userform to a main sub, which passes the code to a worksheet. My problem is...

EXCEL VBA: How to manupulate next cell's (same row) value if cell.value=“WORD” in a range

I want to change the next cell in same row in a if cell.value="word" in a range. I have defined the range, using 'for' loop. In my code, if cell.value="FOUND THE CELL" then cell.value+1="changed the next right side cell" cell.value+2="changed the second right side cell" end if I know this...

Excel - Pulling data from one cell within a list

I use PowerPoint as a graphics template to type up football player names and there squad numbers. It can be a long procedure and so far following YouTube tutorials i have managed to create a form in Excel which can update the text boxes in PowerPoint at the click of...

Excel - select a cell based on adjacent cell value

I have the following excel spreadsheet and I am trying to work out how I can write a formula in order to provide the values in column D. In each row, there is a test date, I am trying to calculate the day difference from each test date to the...

Return #N/A error from ExcelDNA

I'm pretty new to excelDNA, so I may be missing something obvious. I'm trying to return a #N/A from an excelDNA UDF. The function I'm using (via Visual Studio 2010) is below: public static object returnError() { return ExcelDna.Integration.ExcelError.ExcelErrorNA; } When called from an Excel worksheet, this returns a #VALUE...

Excel Search VBA macro

I have been given the task of searching through a large volume of data. The data is presented identically across around 50 worksheets. I need a macro which searches through all these sheets for specific values then copies certain cells to a table created in a new workbook. The macro...

Get IP address of the Network Adapter of a computer having No gateway

I've a project to add persistent routes on a list of servers which I need to do through Powershell. Our environment has 2 NIC's for every server , one production one backup. For this I need to fetch the Backup IP address of the computer , replace the last octet...

How to write formula for cell in Excel?

I'm working on excel where I've few columns. I would like to make SQL insert operation from the records in sheet. There are chances of cell to be empty and this is where I am unable to continue. I need to check: if(cell is empty) insert null else insert value...

How to create a powershell script that triggers a NuGet Update-Package –reinstall?

I have a nuget spec that installs both library file (dll) and two zip packages on a specific folder. Updating the packages is no issue when doing it from Visual Studio 2013 manually. However, when it is being triggered from either our CI servers TeamCity and CCNet, it is only...

Format a command in powershell including a comma, can't find the right way to escape

I have a command that I need to run in a Powershell script, the command is : ".\pacli DELETEUSER DESTUSER='"[email protected]`,com"' sessionid=333" | invoke-expression The comma (,) in here :[email protected],com is not a mistake, and that's what is giving me the hard time. I tried to escape the comma with `...

Which is faster in Excel, an if formula giving 1 or 0 instead of true/false or --?

I've got a large spreadsheet that I'm trying to optimise as it has over 12,000 lines of data, with in excess of 28 columns. It currently takes a significant amount of time to execute and I'm therefore starting to pare it down. As part of this I've started looking at...

adding variables into another variable vba

Dim x As Long Dim y As Long Dim CDTotal As Double Dim CSTotal As Double Dim ETotal As Double Dim FTotal As Double Dim HTotal As Double Dim ITotal As Double Dim ITTotal As Double Dim MTotal As Double Dim TTotal As Double Dim UTotal As Double Dim TotalValue...

PowerShell logic to remove objects from Array

I'm trying to remove objects from an array that contain duplicates and only keep the ones with the highest number in TasteCode. The example below is highly simplified, but it shows the problem. Example: $Fruits Name | Color | TasteCode ----- ------ --------- Apple | Red | 2 Apple |...

How to retrieve the name and path of VM's through powercli

am using the below script to retrieve the NAME and PATH of the VM's and in the PATH am getting the full length path which i dont want it, i just need the path which is displaying after the Resources in the output here is my code: function Get-Path{ param($Object)...

Converting ADODB Loop into DAO

Hi I've been developing a vba project with a lot of help from examples here. I'm trying to access a MS Access database from Excel VBA and import large data sets (500-100+ rows) per request. Currently, the following loop works using ADODB however, the Range("").Copyfromrecordset line is taking very long...

Label linked to a text box value

Good morning, I am editing an User Form on VBA Excel and I would like to show an alert if the user insert a certain value in a text box. I wrote this code: If txtbox.Value < 0 Then lbl_Alert.Visible= True Else lbl_alert.Visible=False End IF The code works properly but...

Converting column from military time to standard time

I'm trying to convert a column showing the time of road traffic accidents from military time to standard time. The data looks like this: Col1 Time..24hr. 1 1404 2 322 3 1945 4 1005 5 945 I'd then like to convert to 12hr so for '322' I'd like to make...

Identifying cell in Openpyxl

I've been working on a project, in which I search an .xlsx document for a cell containing a specific value "x". I've managed to get so far, but I can't extract the location of said cell. This is the code I have come up with: from openpyxl import load_workbook wb...

C# code to add hyper link in excel sheet

Hi I am having an excel file containing multiple sheets. One of the sheets will contain the hyperlink for other sheets. While using the following code I have successfully added the link but when I click on the link it gives error **"Refrence not valid"** Code snippet: private void AddHyperLink(Workbook...

If cell value starts with a specific set of numbers, replace data

My cell values are strings of numbers (always greater than 5 numbers in a cell, ie 67391853214, etc.) If a cell starts with three specific numbers (ie 673 in a cell value 67391853214) I want the data in the cell to be replaced with a different value (if 673 are...

How do I do to count rows in a sheets with filters? With a suppress lines

I have a sheet with lots of columns, but when I filter and use count = Application.WorksheetFunction.CountA(Range("A:A")) It returns all the rows non Empty. Not only the rows I filtered....

if condition inside Insert query in excel?

i'm trying to turn my records in excel into an insert query. i've fields empty in certain situations. In such case, NULL should be inserted. I've written the formula as below but it is not working/showing error. Think i've missed something. ="INSERT INTO table_1 VALUES(" &A2 &",'" & B2 &...

NoClassDefFoundError: UnsupportedFileFormatException while using apache poi to write to an excel file

I am trying to write to an excel(.xlsx) file using Apache poi, I included the apache poi dependencies in my pom.xml file. But I am getting the following exception in execution. Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/poi/UnsupportedFileFormatException at java.lang.ClassLoader.defineClass1(Native Method) at java.lang.ClassLoader.defineClass(ClassLoader.java:800) at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:142) at java.net.URLClassLoader.defineClass(URLClassLoader.java:449) at...

Comparing cell contents against string in Excel

Following is my table file:*.css file:*.csS file:*.PDF file:*.PDF file:*.ppt file:*.xls file:*.xls file:*.doc file:*.doc file:*.CFM file:*.dot file:*.cfc file:*.CFM file:*.CFC file:*.cfc file:*.DOC I need a formula to populate the H column with True or False if it finds column G in column F (exact case). I used following but nothing seems to...

Ordering values from random list in Excel

is there any possibility how to order values from cca 100 rows into table according to two criteria? Compare name and compare Category, or is it bad approach? Lets say i have a list of people: Name Category Value Carl A 10 Carl B 17 John A 11 Jane A...

timestamp SQL to Excel

If this is a duplicate, please let me know, I haven't found anything. I have written a php file that can read content from a database table and write it into a excel .xls file. Everything works fine except by that timestamps. In my generated .xls file every timestamp is...

Join SQL query Results and Get-ChildItem Results

Background: I have a directory with a number of files that are imported to SQL server. Task: Creating a PowerShell script which will pick up files within this directory and use the filenames as in the SQL query. Ultimate objective: To display SQL results besides the filenames but the resultset...

Why doesn't “go get gopkg.in/…” work while “go get github.com/…” OK?

I try to use go get gopkg.in/fatih/pool.v2 to install pool according to Readme.md, but can't success: C:\Users\xiaona\Documents\GitHub> go get -v gopkg.in/fatih/pool.v2 Fetching https://gopkg.in/fatih/pool.v2?go-get=1 https fetch failed. Fetching http://gopkg.in/fatih/pool.v2?go-get=1 import "gopkg.in/fatih/pool.v2": http/https fetch: Get http://gopkg.in/fatih/poo l.v2?go-get=1: dial tcp ConnectEx tcp: A connection attempt failed because the connected party did not...

Excel VBA User-Defined Function: Get Cell in Sheet Function was Called In

I have a user-defined function in Excel that I run in multiple sheets. I am trying to use Cells(i, j) to pull the value of cells by their row and column in the sheet in which my function is called. Instead, Cells(i, j) pulls the value of cell [i,j] in...

Referencing a new inserted column Excel VBA

I am trying to reference a cell in the below formulaes. 'AUA Summary'!$D$9 . Each time the macro runs a new column D is inserted. The Problem: When the column is inserted my reference moves to ** 'AUA Summary'!$E$9. How do I get to reference 'AUA Summary'!$D$9 even if a...

Interface Controls for DoEvent in Excel

I have a macro to loop through a range and return emails to .Display based on the DoEvents element within my module. I iterate that: row_number = 1 'And Do DoEvents row_number = row_number +1 'Then a bunch of formatting requirements Loop Until row_number = 'some value I am wondering...

12 Characters Including leading and following zeros

I am finding this difficult to explain, but ultimately I am wanting a cells value to be 12 characters long including +/- a decimal point and following zeroes. Examples are 1200 would become +1200.000000 -20 would become -20.00000000 99999999 would become +99999999.00 I have tried FIXED, LENGTH, and formatting rules...

Find column with unique values move to the first column and sort worksheets

I have 2 worksheets with the same headers in different orders. Headers are I.D, Name, Department, Sales, Start date, End Date and a few others. What I am aiming to do is search through the workbooks in which the headers may be in different orders, find the column which has...

How to convert excel data to json at frontend side

I want to convert some excel data to JSON. Plan is to get my excel file from D drive, read data and make some UI for this. Can any one please help me out? Data is like this :- country year 1 2 3 4 Netherlands 1970 3603 4330 5080...

excel search engine using vba and filters?

I am using the following vba code to filter my rows in excel based on the value in my cell C5 Sub DateFilter() 'hide dialogs Application.ScreenUpdating = False 'filter for records that have June 11, 2012 in column 3 ActiveSheet.Range("C10:AS30").AutoFilter Field:=1, Criteria1:="*" & ActiveSheet.Range("C5").Value & "*" Application.ScreenUpdating = True End...

VBA “Compile Error: Statement invalid outside Type Block”

I am running a VBA Macro in Excel 2010 with tons of calculations, so data types are very important, to keep macro execution time as low as possible. My optimization idea is to let the user pick what data type all numbers will be declared as (while pointing out the...

Excel 2013 Add a Connector Between Arbitrary Points on Two Different Groups

I'm working in Excel 2013 to (programmatically) add a straight line connector between the lower right hand corner of a rectangle that is part of a grouped shape with the endpoint of a grouped series of line segments. As it stands, I can't even seem to do this manually on...

Excel VBA Loop Delete row does not start with something

I have some data at work looks like this: 00 some data here... 00 some data here... 00 some data here... 00 some data here... Other data I want to remove Other data I want to remove Other data I want to remove Other data I want to remove 00I...

Color a cell on the basis of another cell value

I have a file output.txt having below content: SERVER_NAME MOUNT_POINT TOTAL_SPACE USED_SPACE USED_PERCENTAGE AVAILABLE_SPACE Server_1 /dev 1200G 537G 54% 464G Server_2 /dev 600G 490G 85% 94G Server_3 /dev 600G 402G 69% 181G Backup_server /storage 800G 682G 72% 278G Using following script, I formatted my output.txt file to get a notification...

Sort multiple columns of Excel in VBA given the top-left and lowest-right cell

I am trying to sort these three columns (Sort By Col-2) in excel using VBA. Top-left (Row number and Column number e.g. 1,1) and lowest-right cell (Row number and Column number e.g. 9,3) are known. Every cell contains the values of String type. Input: Col-1 Col-2 Col-3 P1 I1 XYZ...

If statement and addition

I am trying to look if a cell value in second sheet is not equal to blank and then check the value of other cell is lesser than 3 and further update the value to 1 or 0. 1st condition if g2 cell value present in second sheet is not...

How to insert excel formula to cell in Report Builder 3.0

There is RDL report template for SQL Server Reporting Services. I need to set value for cell in table in the report template which must be calculated from other values in the report. When the report is exported to Excel file I need to see the Excel formula in that...

Using VLOOKUP formula or other function to compare two columns

I have one table like this: SHORT TERM BORROWING 1/6/2009 94304 12/31/2010 177823 6/30/2011 84188 12/31/2011 232144 6/30/2012 94467 9/30/2012 91445 12/31/2012 128523 3/31/2013 83731 6/30/2013 78330 9/30/2013 70936 12/31/2013 104020 3/31/2014 62345 6/30/2014 62167 9/30/2014 63494 12/31/2014 104239 3/31/2015 69056 I have another column which lists each date from...

How do I get a cell's position within a range?

How would I go about getting the relative position of a cell within a range? Finding the position of a cell in a worksheet is trivial, using the Row- and Column-properties, but I am unsure of how to do the same within a range. I considered using the position of...

Copying sheet to last row of a sheet from another workbook

I'm stuck in this block of code that copies sheet("Newly Distributed") to the last row of sheet("Source") from another workbook. The error is runtime error 9. What's wrong with my code? Any response would be appreciated. Private Sub copylog3() Dim lRow As Long Dim NextRow As Long, a As Long...

Using a stored integer as a cell reference

Dim x As Integer Dim y As Integer For y = 3 To 3 For x = 600 To 1 Step -1 If Cells(x, y).Value = "CD COUNT" Then Cells(x, y).EntireRow.Select Selection.EntireRow.Hidden = True End if If Cells(x, y).Value = "CD Sector Average" Then Cells(x, y).EntireRow.Select Selection.Insert Shift:=xlDown Cells(x +...

Compare two data sets in different sheet and print difference in other sheet using excel VBA?

I have two data sets in two different sheet Sheet1 is my Orginal ref and sheet2 is for comparison. sheet2 data should get compared by Sheet1 and print entire mismatched row of sheet2 and highlight the cells which has mismatch data and this difference should be printed with column header...

Using a cell's number to insert that many rows (with that row's data)

I have data in excel that looks like this {name} {price} {quantity} joe // 4.99 // 1 lisa // 2.99 // 3 jose // 6.99 // 1 Would it be hard to make a macro that will take the quantity value ("lisa // 3.99 // 3") and add that many...

Using --check on a md5sum command generated checksum file is failing

I'm encountering an error when I try and run md5sum -c on a checksum file I generated. I should mention that I'm running this from PowerShell (as a script will be running this eventually) and this is the cygwin version of md5sum. I have a test file, jira_defect.txt and I've...