• Announcements

    • xper

      MSFN Sponsorship and AdBlockers!   07/10/2016

      Dear members, MSFN is made available via subscriptions, donations and advertising revenue. The use of ad-blocking software hurts the site. Please disable ad-blocking software or set an exception for MSFN. Alternatively, become a site sponsor and ads will be disabled automatically and by subscribing you get other sponsor benefits.
Sign in to follow this  
Followers 0
mchipser

AutoIT excel macro help

3 posts in this topic

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

0

Share this post


Link to post
Share on other sites

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
0

Share this post


Link to post
Share on other sites

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.

0

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

  • Recently Browsing   0 members

    No registered users viewing this page.