Help with script

[smartkid808]

I have a script I am making that will open a file and input and run a macro.

I am getting stuck on getting the macro entered.The macro is good, i just am thinking that it is how i have it coded. Attached is the code. If you want I can paste it here to.

script.au3

Macro for Labels.doc

Edited September 7, 2010 by smartkid808

[taietel]

Smartkid808, welcome to the forum. Try to read in the help file about Excel.udf. 

I've modified a little your script,

expandcollapse popup

ShellExecute("\\oahu1\operations\Purch Labels\test-OPENPO.XLS")
WinWaitActive ("Microsoft Excel")
Sleep(1000)

Send ("{ENTER}")
Sleep(100)
;Add and Run Macro
Send ("!{F8}")
Send ("RepeatRowsOnColumnA")
Send ("{ENTER}")
Sleep(100)
Send ("^a")
Send ("{DELETE}")
;----------------------------------------------------------------------
;                                 MACRO
;----------------------------------------------------------------------
Sleep(100)
Local $macro = "Sub RepeatRowsOnColumnA()" & @CRLF
$macro &= "ActiveSheet.Copy Before:=ActiveSheet" & @CRLF
$macro &= "Application.ScreenUpdating = False" & @CRLF
$macro &= "Dim vRows As Long, v As Long" & @CRLF
$macro &= "On Error Resume Next" & @CRLF
$macro &= "Dim ir As Long, mrows As Long, lastcell As Range" & @CRLF
$macro &= "Set lastcell = Cells.SpecialCells(xlLastCell)" & @CRLF
$macro &= "mrows = lastcell.Row" & @CRLF
$macro &= "For ir = mrows To 2 Step -1" & @CRLF
$macro &= "If Not IsNumeric(Cells(ir, 1)) Then" & @CRLF
$macro &= "Cells(ir, 1).EntireRow.Delete" & @CRLF
$macro &= "ElseIf Cells(ir, 1).Value > 1 Then" & @CRLF
$macro &= "v = Cells(ir, 1).Value - 1" & @CRLF
$macro &= "Rows(ir + 1).Resize(v).Insert Shift:=xlDown" & @CRLF
$macro &= "Rows(ir).EntireRow.AutoFill Rows(ir)."
$macro &= "EntireRow.Resize(rowsize:=v + 1), xlFillCopy" & @CRLF
$macro &= "Rows(ir).EntireRow.Interior.ColorIndex = 36" & @CRLF
$macro &= "ElseIf Cells(ir, 1).Value < 1 Then" & @CRLF
$macro &= "Cells(ir, 1).EntireRow.Delete" & @CRLF
$macro &= "End If" & @CRLF
$macro &= "Next ir" & @CRLF
$macro &= "Application.ScreenUpdating = True" & @LF
ControlSend("Microsoft Visual Basic for Applications","","[CLASS:VbaWindow; INSTANCE:1]",$macro,1)
Send("{F5}")
Send("!q")

M.I.

[Realm]

If you want I can paste it here to.

Most ppl don't want to take the time to download files to help, it would be better if you included the code in AutoIt tags by pasting your code in between [AutoIt ][/AutoIt ]...just delete the trailing spaces.

Edit: I stand corrected. There are a few that don't mind

Edited September 8, 2010 by Realm

[taietel]

Realm, you are right regarding the way he posted, but I didn't mind because it is his first post and I'm very tired (3.52 AM here) 

[bogQ]

sugestion on sugestion

expandcollapse popup

ShellExecute("\\oahu1\operations\Purch Labels\test-OPENPO.XLS")
WinWaitActive ("Microsoft Excel")
Sleep(1000)


Send ("{ENTER}")
Sleep(100)
;Add and Run Macro
Send ("!{F8}")
Send ("RepeatRowsOnColumnA")
Send ("{ENTER}")
Sleep(100)
Send ("^a")
Send ("{DELETE}")
;~ ;----------------------------------------------------------------------
;~ ;                                 MACRO
;~ ;----------------------------------------------------------------------
WinWaitActive("Microsoft Visual Basic")
Local $macro = "Sub RepeatRowsOnColumnA()" & @CRLF
$macro &= "ActiveSheet.Copy Before:=ActiveSheet" & @CRLF
$macro &= "Application.ScreenUpdating = False" & @CRLF
$macro &= "Dim vRows As Long, v As Long" & @CRLF
$macro &= "On Error Resume Next" & @CRLF
$macro &= "Dim ir As Long, mrows As Long, lastcell As Range" & @CRLF
$macro &= "Set lastcell = Cells.SpecialCells(xlLastCell)" & @CRLF
$macro &= "mrows = lastcell.Row" & @CRLF
$macro &= "For ir = mrows To 2 Step -1" & @CRLF
$macro &= "If Not IsNumeric(Cells(ir, 1)) Then" & @CRLF
$macro &= "Cells(ir, 1).EntireRow.Delete" & @CRLF
$macro &= "ElseIf Cells(ir, 1).Value > 1 Then" & @CRLF
$macro &= "v = Cells(ir, 1).Value - 1" & @CRLF
$macro &= "Rows(ir + 1).Resize(v).Insert Shift:=xlDown" & @CRLF
$macro &= "Rows(ir).EntireRow.AutoFill Rows(ir)."
$macro &= "EntireRow.Resize(rowsize:=v + 1), xlFillCopy" & @CRLF
$macro &= "Rows(ir).EntireRow.Interior.ColorIndex = 36" & @CRLF
$macro &= "ElseIf Cells(ir, 1).Value < 1 Then" & @CRLF
$macro &= "Cells(ir, 1).EntireRow.Delete" & @CRLF
$macro &= "End If" & @CRLF
$macro &= "Next ir" & @CRLF
$macro &= "Application.ScreenUpdating = True" & @CRLF
$macro &= "End Sub" & @LF
ClipPut($macro)
Send("^v")
Send("{F5}")
Send("!q")

Edited September 8, 2010 by bogQ

[smartkid808]

Thanks guys for your help. I think it will work. I will have the user who needs it try it tomorrow. I will also for future post post the actual code instead of uploading it.

Thanks

Will