smartkid808 Posted September 7, 2010 Share Posted September 7, 2010 (edited) 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.au3Macro for Labels.doc Edited September 7, 2010 by smartkid808 Link to comment Share on other sites More sharing options...
taietel Posted September 8, 2010 Share Posted September 8, 2010 Smartkid808, welcome to the forum. Try to read in the help file about Excel.udf. I've modified a little your script, expandcollapse popupShellExecute("\\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. Things you should know first...In the beginning there was only ONE! And zero... Progs: Create PDF(TXT2PDF,IMG2PDF) 3D Bar Graph DeskGadget Menu INI Photo Mosaic 3D Text Link to comment Share on other sites More sharing options...
Realm Posted September 8, 2010 Share Posted September 8, 2010 (edited) 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 My Contributions: Unix Timestamp: Calculate Unix time, or seconds since Epoch, accounting for your local timezone and daylight savings time. RegEdit Jumper: A Small & Simple interface based on Yashied's Reg Jumper Function, for searching Hives in your registry. Link to comment Share on other sites More sharing options...
taietel Posted September 8, 2010 Share Posted September 8, 2010 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) Things you should know first...In the beginning there was only ONE! And zero... Progs: Create PDF(TXT2PDF,IMG2PDF) 3D Bar Graph DeskGadget Menu INI Photo Mosaic 3D Text Link to comment Share on other sites More sharing options...
bogQ Posted September 8, 2010 Share Posted September 8, 2010 (edited) sugestion on sugestion expandcollapse popupShellExecute("\\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 TCP server and client - Learning about TCP servers and clients connectionAu3 oIrrlicht - Irrlicht projectAu3impact - Another 3D DLL game engine for autoit. (3impact 3Drad related) There are those that believe that the perfect heist lies in the preparation.Some say that it’s all in the timing, seizing the right opportunity. Others even say it’s the ability to leave no trace behind, be a ghost. Link to comment Share on other sites More sharing options...
smartkid808 Posted September 8, 2010 Author Share Posted September 8, 2010 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 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now