Jump to content
Strawberry Orange Banana Lime Leaf Slate Sky Blueberry Grape Watermelon Chocolate Marble
Strawberry Orange Banana Lime Leaf Slate Sky Blueberry Grape Watermelon Chocolate Marble

MSFN is made available via donations, subscriptions and advertising revenue. The use of ad-blocking software hurts the site. Please disable ad-blocking software or set an exception for MSFN. Alternatively, register and become a site sponsor/subscriber and ads will be disabled automatically. 

Sign in to follow this  
mchipser

AutoIT excel macro help

Recommended Posts

mchipser    0

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

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
mchipser    0

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)

Edited by mchipser

Share this post


Link to post
Share on other sites
Tripredacus    286

I also see that you set your variable $write to be nothing (which is fine) and you check it to see if there is data in it. AutoIT has this function already, IsObj. Look at this I typed up real quick:

dim $var
$var = ClipGet()

IF IsObj($var) Then
FileWriteLine($file,$var)
ELSE
sleep(100)
endif

I haven't tested this of course so hopefully I didn't make an error.

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  

  • Recently Browsing   0 members

    No registered users viewing this page.

×