ImportExcel resetting formula

Question

Hi I had a nice (albeit long) Powershell script that would pull information from separate spreadsheets and put it into a single one (on specific tabs) since that stopped working and I'm starting to try to create a new one using the ImportExcel module I've got the first part of that working but when I put the information into ColumnA the vlookup I had in ColumnB is altered.
The formula to begin with in ColumnB is =IF(ISNA(VLOOKUP(A2,Webapp!A:A,1,FALSE)),"MISSING","")
After adding information to ColumnA it is =IF(ISNA(VLOOKUP(A2,A:A,1,FALSE)),"MISSING","")
Is this expecting behaviour and if not could someone help me understand why it's doing it and stop it from happening please

The code I'm using to add the information is as follows
Import-Excel "\share\$Date\ADUsers.xlsx" -WorksheetName "Sheet1" -ImportColumns @(1) -StartRow 1 -NoHeader -DataOnly | Export-Excel -Path "\share\$date\Comparison.xlsx" -WorksheetName "AD Account" -StartColumn 1 -StartRow 2 -NoHeader -NoLegend

Tony

Answer 1

Humor someone that knows only the basics of Excel?

Since I can't upload an Excell spreadsheet, graphics are going to have to suffice. Are the two worksheets in the attached graphics files representative of what you have?

Answer 2

Hi

Thank you for posting your query.

Kindly try the below PowerShell Function and check if it resolve the issue.

function Import-Excel

{

[CmdletBinding(SupportsShouldProcess=$true)]   

Param(   
    [parameter(   
        mandatory=$true,    
        position=1,    
        ValueFromPipeline=$true,    
        ValueFromPipelineByPropertyName=$true)]   
    [String[]]   
    $Path,   

    [parameter(mandatory=$false)]   
    $Worksheet = 1,   

    [parameter(mandatory=$false)]   
    [switch]   
    $Force   
)   

Begin   
{   
    function GetTempFileName($extension)   
    {   
        $temp = [io.path]::GetTempFileName();   
        $params = @{   
            Path = $temp;   
            Destination = $temp + $extension;   
            Confirm = $false;   
            Verbose = $VerbosePreference;   
        }   
        Move-Item @params;   
        $temp += $extension;   
        return $temp;   
    }   

    # since an extension like .xls can have multiple formats, this   
    # will need to be changed   
    #   
    $xlFileFormats = @{   
        # single worksheet formats   
        '.csv'  = 6;        # 6, 22, 23, 24   
        '.dbf'  = 11;       # 7, 8, 11   
        '.dif'  = 9;        #    
        '.prn'  = 36;       #    
        '.slk'  = 2;        # 2, 10   
        '.wk1'  = 31;       # 5, 30, 31   
        '.wk3'  = 32;       # 15, 32   
        '.wk4'  = 38;       #    
        '.wks'  = 4;        #    
        '.xlw'  = 35;       #    

        # multiple worksheet formats   
        '.xls'  = -4143;    # -4143, 1, 16, 18, 29, 33, 39, 43   
        '.xlsb' = 50;       #   
        '.xlsm' = 52;       #   
        '.xlsx' = 51;       #   
        '.xml'  = 46;       #   
        '.ods'  = 60;       #   
    }   

    $xl = New-Object -ComObject Excel.Application;   
    $xl.DisplayAlerts = $false;   
    $xl.Visible = $false;   
}   

Process   
{   
    $Path | ForEach-Object {   

        if ($Force -or $psCmdlet.ShouldProcess($_)) {   

            $fileExist = Test-Path $_   

            if (-not $fileExist) {   
                Write-Error "Error: $_ does not exist" -Category ResourceUnavailable;               
            } else {   
                # create temporary .csv file from excel file and import .csv   
                #   
                $_ = (Resolve-Path $_).toString();   
                $wb = $xl.Workbooks.Add($_);   
                if ($?) {   
                    $csvTemp = GetTempFileName(".csv");   
                    $ws = $wb.Worksheets.Item($Worksheet);   
                    $ws.SaveAs($csvTemp, $xlFileFormats[".csv"]);   
                    $wb.Close($false);   
                    Remove-Variable -Name ('ws', 'wb') -Confirm:$false;   
                    Import-Csv $csvTemp;   
                    Remove-Item $csvTemp -Confirm:$false -Verbose:$VerbosePreference;   
                }   
            }   
        }   
    }   
}   

End   
{   
    $xl.Quit();   
    Remove-Variable -name xl -Confirm:$false;   
    [gc]::Collect();   
}   

}

-------------------------------------------------------------------------------------------------------------------------------

If the answer is helpful kindly click "Accept as Answer" and upvote it. Thanks.