Welcome to MSFN

Register now to gain access to all of our features. Once registered and logged in, you will be able to contribute to this site by submitting your own content or replying to existing content. You'll be able to customize your profile, receive reputation points as a reward for submitting content, while also communicating with other members via your own private inbox, plus much more! This message will be removed once you have signed in.


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.