Jump to content
xcaliber13

Excel Help

Recommended Posts

xcaliber13

Hello,

           Could someone point me in the right direction on this.   I am stuck.  I have a text file that I pull into Excel.  No problem with that. Now I have to move some data but not sure how to do that.   If any cell in column C has data shift that row From columns E:AZ one cell to the right. I got as far as being able to select the cells in column C that has data in them. But What now?

Local $oExcel = ObjCreate("Excel.Application")
Const $xlToRight = -4161
Global $sPathExcel = "Path to test.xls"
Global $oExcel = _Excel_Open()
Global $sTextFile = "Path to Input.txt"
Global $oWorkBook = _Excel_BookOpenText($oExcel, $sTextFile, 1, $xlDelimited, $xlTextQualifierDoubleQuote, False, "|", Default, Default, Default, True)
$oWorkbook.ActiveSheet.Range("A:E").EntireColumn.Delete
$oRange = $oWorkBook.ActiveSheet.Range("C:C").Cells.SpecialCells($xlCellTypeConstants).Select

Just  blank on what to do now.   Do I do a for loop?   Select and cut and paste?

Thank you

Share this post


Link to post
Share on other sites
water

Use _Excel_RangeInsert to insert an empty cell at column E and shift everything else to the right. 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
xcaliber13

Water,

      Thank you for the reply.   I am selecting data in column C because that column should be empty. But what is happening is that some of the data has an extra field in it. So when it gets pulled into Excel  it is making the data misalign. Example:

     A                    B                                C                                 D                                           E               F         G         H

LastName     FirstName    Should be blank column     Address                                 Data         Data   Data

LastName     FirstName    Should be blank column     Address                                 Data         Data   Data

LastName      FirstName        MiddlleName                   Should be blank Column    Address   Data   Data   Data

LastName     FirstName    Should be blank column     Address                                 Data         Data   Data

LastName      FirstName        MiddlleName                   Should be blank Column    Address   Data   Data   Data

So what I am trying to do is select cell in column c that has data in it and than shift the remaining of that row to the right.  Not understanding how _Excel_RangeInsert will do that.

Share this post


Link to post
Share on other sites
dmob

I would read the data into an array, do the checking and shifting then write the data back into the sheet, or insert new sheet with arranged data, but I could have misunderstood what you want to do?

Share this post


Link to post
Share on other sites
xcaliber13

This is what I have worked on but still not moving the data

Local $oExcel = ObjCreate("Excel.Application")
Const $xlToRight = -4161
Global $sPathExcel = "Path to test.xls"
Global $oExcel = _Excel_Open()
Global $sTextFile = "Path to Input.txt"
Global $oWorkBook = _Excel_BookOpenText($oExcel, $sTextFile, 1, $xlDelimited, $xlTextQualifierDoubleQuote, False, "|", Default, Default, Default, True)
$oWorkbook.ActiveSheet.Range("A:E").EntireColumn.Delete
$oRange = $oWorkBook.ActiveSheet.Range("C:C").Cells.SpecialCells($xlCellTypeConstants).Select

For $i = 320 To 1 Step -1
     If _Excel_RangeRead($oWorkbook, Default, $oRange & $i) > 1  Then
        $oExcel.Range("E:AZ").Rows($i).Selection
        $oExcel.Range("E:AZ").Rows($i).Cut
        $oExcel.Range("D:D").Rows($i).Selection
        $oExcel.Range("D:D").Rows($i).Paste
    EndIF
Next

Any ideas anyone?   Script completes with no errors.  But does not move the data over to the right.

Share this post


Link to post
Share on other sites
water

First of all: Drop this the following line. You are starting Excel two times.

Local $oExcel = ObjCreate("Excel.Application")

_Excel_RangeInsert inserts an empty cell and moves all existing data one to the right. What have you tried so far with _Excel_RangeInsert.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
water

Example:

#include <Excel.au3>
Global $aArray[][] = [["LastName1", "FirstName1", "", "Address", "Data", "Data", "Data"], _
["LastName2", "FirstName2", "", "Address", "Data", "Data", "Data"], _
["LastName3", "FirstName3", "MiddleName", "", "Data", "Data", "Data"], _
["LastName4", "FirstName4", "", "Address", "Data", "Data", "Data"], _
["LastName5", "FirstName5", "MiddleName", "", "Data", "Data", "Data"]]
Global $oRange
Global $oExcel = _Excel_open()
Global $oWorkbook = _Excel_BookNew($oExcel)
Global $oRange = _Excel_RangeWrite($oWorkbook, Default, $aArray, "A1")
Global $iLastRow = $oWorkBook.Activesheet.UsedRange.Rows.Count
MsgBox(0, "", "Now shifting data")
For $i = 1 To $iLastRow
    $sCell = _Excel_RangeRead($oWorkbook, Default, "C" & $i)
    If $sCell <> "" Then _Excel_RangeInsert($oWorkBook.Activesheet, "C" & $i, $xlShiftToRight)
Next

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
xcaliber13

Water,

          My sincere apologies for wasting your time.  Your script works perfectly and after re reading my posts I found why we were not on the same page.  And why I was not understanding  _Excel_RangeInsert.    I really need the data to shift to the left.  ( Yeah, I hear ya what a dumb ass)  Dam old age is getting me.  I did try changing to $xlShiftToLeft but that did nothing.  I will keep  working on it. But any ideas would be greatly appreciated.   Again sorry for wasting your time.

Share this post


Link to post
Share on other sites
water

With _Excel_RangeInsert only $xlShiftToRight or $xlShiftDown are valid (according to the help file).
You would need to delete a range to use $ xlShiftToLeft or $xlShiftUp.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
xcaliber13

Water

         Thank you for all your help.  Once again you have pointed me the right direction and I was able to complete the script.  So between using  _Excel_RangeInsert and _Excel_RangeDelete I was able to get all data into the correct columns.   Again thank  you

Share this post


Link to post
Share on other sites
water

Glad to be of service :)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.