Sign in to follow this  
Followers 0
myk3

excel vba.. Deselect active row / cell

4 posts in this topic

#1 ·  Posted (edited)

I can not find this anywhere..

I have this code

$file = FileOpen(@ScriptDir & "\test.txt", 1)
$oExcel = _ExcelBookOpen(@ScriptDir & "\Master List.xls",0)
$oExcel.sheets("Non-Compliant").Select
Do 
$oExcel.cells.Find($NewArray[$1]).select
$oExcel.ActiveCell.entirerow.copy
FileWriteLine($file,Clipget())
$1 += 1
Until $1 = UBound($NewArray)
_ExcelBookClose($oExcel)

Everything works however if the array variable is not found it copies the previous selection into the "test.txt file

Is there a way to deselect or an if statement to use in conjunction with the vba code?

I tried

$file = FileOpen(@ScriptDir & "\test.txt", 1)
$oExcel = _ExcelBookOpen(@ScriptDir & "\Master List.xls",0)
$oExcel.sheets("Non-Compliant").Select
Do 
if $oExcel.cells.Find($NewArray[$1]).select = 0 Then
    $oExcel.ActiveCell.entirerow.copy
    FileWriteLine($file,Clipget())
    endif 
$1 += 1
Until $1 = UBound($NewArray)
_ExcelBookClose($oExcel)

which did not work

Edited by myk3

Share this post


Link to post
Share on other sites



I think I got something that will work but don't know how to pass a macro to excel from autoit

***********
EXCEL MACRO
***********
Sub UnSelectCurrentArea()
    Dim Area As Range
    Dim RR As Range
    
    For Each Area In Selection.Areas
        If Application.Intersect(Area, ActiveCell) Is Nothing Then
            If RR Is Nothing Then
                Set RR = Area
            Else
                Set RR = Application.Union(RR, Area)
            End If
        End If
    Next Area
    If Not RR Is Nothing Then
        RR.Select
    End If
End Sub

Share this post


Link to post
Share on other sites

just googled : "excel object run macro", I believe you will fill up your need up there.

hench

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

I got it working with a simple if statement in autoit... but for some odd reason when I open the excel doc it always opens to 1 cell and copies it.. odd

#include <Excel.au3>
#include <Array.au3>
#include <file.au3>
;~ ;**************************
;~ ;read array from text file
;~ ;**************************
FileChangeDir (@scriptdir )
If FileExists ("array.txt")<>1 then 
    MsgBox(0, "ERROR", "Array text file not found   ")
    Exit
    EndIf
Dim $NewArray
FileOpen("array.txt",0)
If Not _FileReadToArray("array.txt",$NewArray) Then
   MsgBox(4096,"Error", " Error reading text file to $NewArray   error:" & @error)
   EndIf
FileClose("array.txt") 
$1=1
$write = ""
;******************************************************************************
; Open excel query each item in the array and post it to a document to print
;******************************************************************************
$file = FileOpen(@ScriptDir & "\test.txt", 2)
$oExcel = _ExcelBookOpen(@ScriptDir & "\Master List.xls",0)
$oExcel.sheets("Non-Compliant").Select
Do 
$oExcel.cells.Find($NewArray[$1]).activate
$oExcel.ActiveCell.entirerow.copy
if ClipGet() = $write Then
sleep(100)
else
    $write = clipget()
FileWriteLine($file,$write)
endif
$1 += 1
Until $1 = UBound($NewArray)
_ExcelBookClose($oExcel)

When Opening the excel doc I had a macro that was selecting that line.. it is fixed now :x

Edited by myk3

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
Sign in to follow this  
Followers 0