Jump to content

Welcome to MSFN Forum
Register now to gain access to all of our features. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more. This message will be removed once you have signed in.
Login to Account Create an Account


Photo

AutoIT excel macro help

- - - - -

  • Please log in to reply
2 replies to this topic

#1
mchipser

mchipser

    Junior

  • Member
  • Pip
  • 69 posts
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



How to remove advertisement from MSFN

#2
mchipser

mchipser

    Junior

  • Member
  • Pip
  • 69 posts
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, 01 January 2011 - 01:41 PM.


#3
Tripredacus

Tripredacus

    K-Mart-ian Legend

  • Super Moderator
  • 9,706 posts
  • OS:Server 2012
  • Country: Country Flag

Donator

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.
MSFN RULES | GimageX HTA for PE 3.x | lol probloms
msfn2_zpsc37c7153.jpg




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users



How to remove advertisement from MSFN