Excel Help

[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

[water]

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

[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.

[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?

[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.

[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.

[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

 

[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.

[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.

[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

[water]

Glad to be of service