ToyBoi Posted July 10, 2008 Share Posted July 10, 2008 Hi, I think this should be a quick one, but thanks for the help in advance I have a macro in excel which imports txt files and then does other formatting work afterwards the problem is, everytime it imports, it asks the user for confirmation. The user has to press the button "Import" This is annoying b/c the macro imports many files, and it requires the user to stick around for a long time. can i have autoit call a macro stored in my personal.xls and send a keystroke when asked to do so by excel? or second option is to have excel macro call an autoit script which sends a keystroke. is this doable, and if so what is the command for that also what is the command for autoit to select a sheet with a specific name, say "test" Link to comment Share on other sites More sharing options...
Moderators big_daddy Posted July 10, 2008 Moderators Share Posted July 10, 2008 What method is the macro calling that causes the conformation. Link to comment Share on other sites More sharing options...
Jeemo Posted July 11, 2008 Share Posted July 11, 2008 Can you post your Excel VBA code? A quick mod will probably suppress that prompt without having to involve AutoIT. An emoticon is worth a dozen words. Link to comment Share on other sites More sharing options...
ToyBoi Posted July 11, 2008 Author Share Posted July 11, 2008 (edited) this is my code for the macro, it's quite long my macro, first parses 6 txt files. each txt file need to be divided into 6 smaller txt files so eg, if my original txt was called cm1.txt i would have 6 txt files that start with "cm1", the names are always static and don't change. since this thing i did is a little buggy, it makes a bunch of extra txt files, so i have to use kill to delete them, but they don't cause any problems. i bodled the part where i needed to import, since i haven't done importing macros, so i just recorded that part, and everytime it does that, it asks the user to press the button "Import" expandcollapse popupSub ParseMasterTXTFile() '******************************************************************************************* '******************************************************************************************* '******************************************************************************************* '******************************************************************************************* 'DO NOT EDIT THIS FILE!!!!!!!!!!!!!!!!!!!!! '******************************************************************************************* '******************************************************************************************* '******************************************************************************************* '******************************************************************************************* Dim fn As Integer, i As Integer, sBuf1 As String, sBuf2 As String, sBasePath As String Dim ImportPath As String Dim Filename As String Dim number As Integer Dim size As Long Dim LNewWB As String 'new workbook's name Dim LMainWB As String ' Dim NumArray(1 To 50) As String Dim MainArray(1 To 7) As Integer MainArray(1) = 1 MainArray(2) = 3 MainArray(3) = 5 MainArray(4) = 7 MainArray(5) = 11 MainArray(6) = 13 MainArray(7) = 19 LMainWB = ActiveWorkbook.Name ImportPath = ActiveWorkbook.path Set NewBook = Workbooks.Add With NewBook .Title = "All" .Subject = "All" .SaveAs Filename:="All.xls" End With LNewWB = ActiveWorkbook.Name Windows(LMainWB).Activate '******************************************************************************************* '******************************************************************************************* '******************************************************************************************* '******************************************************************************************* 'loop '******************************************************************************************* '******************************************************************************************* '******************************************************************************************* '******************************************************************************************* For c = 1 To 7 Filename = "cm" & CStr(MainArray(c)) 'Filename = InputBox(Prompt:="Enter the filename 'Example cm1' make sure that the file is a txt file! Also remember to delte the first few extra lines at the top, so the first line is [cluster] **do not enter the extension so don't enter cm1.txt **", _ Title:="Enter q when you have no more files to parse", Default:="filename") '******************************************************************************************* '******************************************************************************************* '******************************************************************************************* '******************************************************************************************* ' find out what number cm this is '******************************************************************************************* '******************************************************************************************* '******************************************************************************************* '******************************************************************************************* number = MainArray(c) ' For b = 1 To 100 ' If Filename = "cm" & CStr(b) Then ' number = b ' b = 99 ' ElseIf Filename = "CM" & CStr(b) Then ' number = b ' b = 99 ' End If ' Next b '******************************************************************************************* '******************************************************************************************* '******************************************************************************************* '******************************************************************************************* 'end '******************************************************************************************* '******************************************************************************************* '******************************************************************************************* '******************************************************************************************* '******************************************************************************************* '******************************************************************************************* '******************************************************************************************* '******************************************************************************************* 'end '******************************************************************************************* '******************************************************************************************* '******************************************************************************************* '******************************************************************************************* '******************************************************************************************* '******************************************************************************************* '******************************************************************************************* '******************************************************************************************* ' find out what number cm this is '******************************************************************************************* '******************************************************************************************* '******************************************************************************************* '******************************************************************************************* ' For b = 1 To 100 ' If Filename = "cm" & CStr(b) Then ' number = b ' b = 99 ' ElseIf Filename = "CM" & CStr(b) Then ' number = b ' b = 99 ' End If ' Next b 'DynamicArray(n) = number Windows(LMainWB).Activate path = ActiveWorkbook.path sBasePath = ActiveWorkbook.path & "\" & Filename sBuf1 = "" sBuf2 = "" i = 0 fn = FreeFile Open sBasePath & ".txt" For Input As #fn Do While Not EOF(fn) Line Input #fn, sBuf1 If sBuf1 <> "" Then sBuf2 = sBuf2 & sBuf1 & vbCrLf Else i = i + 1 SaveTextFile sBasePath & CStr(i) & ".txt", sBuf2, True sBuf2 = "" End If Loop '******************************************************************************************* '******************************************************************************************* '******************************************************************************************* '******************************************************************************************* ' deletes files that do not need '******************************************************************************************* '******************************************************************************************* '******************************************************************************************* '******************************************************************************************* ' Kill ActiveWorkbook.Path & "\" & "cm*1.txt" ' Kill ActiveWorkbook.Path & "\" & "cm?3.txt" ' Kill ActiveWorkbook.Path & "\" & "cm*13.txt" ' Kill ActiveWorkbook.Path & "\" & "cm*5.txt" ' Kill ActiveWorkbook.Path & "\" & "cm*7.txt" ' Kill ActiveWorkbook.Path & "\" & "cm*9.txt" ' Kill ActiveWorkbook.Path & "\" & "cm*16.txt" ' Kill ActiveWorkbook.Path & "\" & "cm*18.txt" ' Kill ActiveWorkbook.Path & "\" & "cm?2.txt" If c = 1 Then Kill ActiveWorkbook.path & "\" & "cm12.txt" Kill ActiveWorkbook.path & "\" & "cm15.txt" Kill ActiveWorkbook.path & "\" & "cm17.txt" Kill ActiveWorkbook.path & "\" & "cm111.txt" Kill ActiveWorkbook.path & "\" & "cm113.txt" Kill ActiveWorkbook.path & "\" & "cm115.txt" Kill ActiveWorkbook.path & "\" & "cm116.txt" Kill ActiveWorkbook.path & "\" & "cm117.txt" Kill ActiveWorkbook.path & "\" & "cm118.txt" Else Kill ActiveWorkbook.path & "\" & "cm" & CStr(number) & "3.txt" Kill ActiveWorkbook.path & "\" & "cm" & CStr(number) & "5.txt" Kill ActiveWorkbook.path & "\" & "cm" & CStr(number) & "7.txt" Kill ActiveWorkbook.path & "\" & "cm" & CStr(number) & "9.txt" Kill ActiveWorkbook.path & "\" & "cm" & CStr(number) & "11.txt" Kill ActiveWorkbook.path & "\" & "cm" & CStr(number) & "13.txt" Kill ActiveWorkbook.path & "\" & "cm" & CStr(number) & "15.txt" Kill ActiveWorkbook.path & "\" & "cm" & CStr(number) & "16.txt" Kill ActiveWorkbook.path & "\" & "cm" & CStr(number) & "17.txt" Kill ActiveWorkbook.path & "\" & "cm" & CStr(number) & "18.txt" Kill ActiveWorkbook.path & "\" & "cm" & CStr(number) & "1.txt" Kill ActiveWorkbook.path & "\" & "cm" & CStr(number) & "2.txt" End If '******************************************************************************************* '******************************************************************************************* ' rename files '******************************************************************************************* '******************************************************************************************* 'Dim OldFileName As String 'Dim NewFileName As String 'OldFileName = ActiveWorkbook.Path & "\" & "cm?4.txt" 'NewFileName = ActiveWorkbook.Path & "\" & "cluster.txt" 'Name OldFileName As NewFileName 'Name ActiveWorkbook.Path & "\" & "cm?4.txt" As ActiveWorkbook.Path & "\" & "cluster.txt" 'Name ActiveWorkbook.Path & "\" & "cm*6.txt" As ActiveWorkbook.Path & "\" & "slicegroup.txt" 'Name ActiveWorkbook.Path & "\" & "cm*2.txt" As ActiveWorkbook.Path & "\" & "nodegroup.txt" 'Name ActiveWorkbook.Path & "\" & "cm*8.txt" As ActiveWorkbook.Path & "\" & "slice.txt" 'Name ActiveWorkbook.Path & "\" & "cm*14.txt" As ActiveWorkbook.Path & "\" & "dnp.txt" 'Name ActiveWorkbook.Path & "\" & "cm*0.txt" As ActiveWorkbook.Path & "\" & "pipe.txt" '******************************************************************************************* '******************************************************************************************* ' importing '******************************************************************************************* '******************************************************************************************* Windows(LMainWB).Activate Sheets("Reworked").Select Range("I1") = "Input TSID" Range("N1") = "Output TSID" Sheets("clusters").Select Range("A2").Select With Selection.QueryTable .Connection = _ "TEXT;" & ImportPath & "\cm" & CStr(number) & "4.txt" .TextFilePlatform = 437 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = True .TextFileTabDelimiter = False .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = True .TextFileColumnDataTypes = Array(2, 9, 1, 1, 1, 1, 1, 1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Sheets("Slice groups").Select Range("A2").Select With Selection.QueryTable .Connection = _ "TEXT;" & ImportPath & "\cm" & CStr(number) & "6.txt" .TextFilePlatform = 1251 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = True .TextFileTabDelimiter = False .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = True .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Sheets("Nodegroups").Select Range("A2").Select With Selection.QueryTable .Connection = _ "TEXT;" & ImportPath & "\cm" & CStr(number) & "12.txt" .TextFilePlatform = 437 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = True .TextFileTabDelimiter = False .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = True .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Sheets("Slices").Select Range("A2").Select With Selection.QueryTable .Connection = _ "TEXT;" & ImportPath & "\cm" & CStr(number) & "8.txt" .TextFilePlatform = 437 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = True .TextFileTabDelimiter = False .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = True .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Sheets("DNP").Select Range("A2").Select With Selection.QueryTable .Connection = _ "TEXT;" & ImportPath & "\cm" & CStr(number) & "14.txt" .TextFilePlatform = 437 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = True .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = True .TextFileColumnDataTypes = Array(1, 9, 1, 1, 1, 1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Sheets("Pipes").Select Range("E1").Select With Selection.QueryTable .Connection = _ "TEXT;" & ImportPath & "\cm" & CStr(number) & "10.txt" .TextFilePlatform = 437 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = True .TextFileTabDelimiter = False .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = True .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _ 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With ' finds out how long many entries there are in the file size = 1 Sheets("DNP").Select While Len(Range("A" & CStr(size)).Value) <> 0 size = size + 1 Wend size = size - 1 Sheets("Reworked").Select Range("A2" & ":S" & CStr(size)).Select Selection.FillDown If c = 1 Then Kill ActiveWorkbook.path & "\" & "cm14.txt" Kill ActiveWorkbook.path & "\" & "cm16.txt" Kill ActiveWorkbook.path & "\" & "cm18.txt" Kill ActiveWorkbook.path & "\" & "cm110.txt" Kill ActiveWorkbook.path & "\" & "cm112.txt" Kill ActiveWorkbook.path & "\" & "cm114.txt" Else Kill ActiveWorkbook.path & "\" & "cm" & CStr(number) & "4.txt" Kill ActiveWorkbook.path & "\" & "cm" & CStr(number) & "6.txt" Kill ActiveWorkbook.path & "\" & "cm" & CStr(number) & "12.txt" Kill ActiveWorkbook.path & "\" & "cm" & CStr(number) & "8.txt" Kill ActiveWorkbook.path & "\" & "cm" & CStr(number) & "14.txt" Kill ActiveWorkbook.path & "\" & "cm" & CStr(number) & "10.txt" End If '******************************************************************************************* '******************************************************************************************* '******************************************************************************************* '******************************************************************************************* ' saving '******************************************************************************************* '******************************************************************************************* '******************************************************************************************* '******************************************************************************************* ' fileSaveName = Application.GetSaveAsFilename( _ ' fileFilter:="Excel Files (*.xls), *.xls") 'If user specified file name, perform Save and display msgbox ' If fileSaveName <> False Then ' ActiveWorkbook.SaveAs Filename:=fileSaveName, FileFormat:=xlNormal '******************************************************************************************* '******************************************************************************************* ' deletes entries in the reworked page so its ready for the new cm '******************************************************************************************* '******************************************************************************************* Sheets("Reworked").Select Range("A1" & ":S" & CStr(size)).Select Selection.Copy 'go to the all workbook Windows(LNewWB).Activate Sheets.Add Sheets(1).Name = "cm" & CStr(number) Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues ' go back to the main workbook Windows(LMainWB).Activate Range("A3" & ":S" & CStr(size)).Select Selection.Delete ' End If Next c Windows(LMainWB).Activate Sheets("README!!").Select Windows(LNewWB).Activate Sheets("Sheet1").Delete Sheets("Sheet2").Delete Sheets("Sheet3").Delete MsgBox "Robert has finished your copy. " Exit Sub End Sub Function SaveTextFile(strFile As String, strData As String, Optional bOverWrite As Boolean = False) As Boolean Dim iHandle As Integer, l As Long If Not bOverWrite Then If Len(Dir(strFile)) > 0 Then SaveTextFile = False Exit Function End If End If iHandle = FreeFile l = Len(strData) Open strFile For Binary Access Write As #iHandle Len = 2 Put #iHandle, , strData Close #iHandle SaveTextFile = True End Function Function Combine() End Function Edited July 11, 2008 by ToyBoi Link to comment Share on other sites More sharing options...
try67 Posted July 11, 2008 Share Posted July 11, 2008 Couldn't spot the place where the user is prompted to click on "Import", but you can easily call an AutoIt script from Excel using the Shell command: Shell (pathname[,windowstyle]) If you use "0" for windowstyle, the script will be hidden. Link to comment Share on other sites More sharing options...
ToyBoi Posted July 11, 2008 Author Share Posted July 11, 2008 so this is what i put in there Shell ImportPath & "\Enter.au3" Importpath is a string which is activeworkbook.path enter.au3 is in the same folder and the code for that is Send("{ENTER}") Exit but i'm getting errors, are my syntaxes correct? Link to comment Share on other sites More sharing options...
Jeemo Posted July 11, 2008 Share Posted July 11, 2008 @ToyBoi, that script is way too long to try to visually spot the line that's causing the prompt to the user. You can find it, though, if you do this (it seems like a lot, but it'll just take a minute):Go to Excel and open up the file on which you would normally run this macroHit Alt+F11 to open the VB EditorBrowse to the module within Personal.xls that contains the code you pastedYou can "Step through" the code by pressing F8. Each press of F8 processes one line of code. If the initial press of F8 does not initiate a Step-through, go to the first line of code and press F9. This will insert a Break Point - the code processing halts every time it reaches a Break Point, and you can Step Through from that point forward. So insert the Break Point and then invoke the macro however you normally do.Press and hold F8 and it will quickly run through the code until it reaches a message box or anything else the prompts the user in any way.Keep doing this until you get to your prompt that you want to get rid of. Whatever line of code is highlighted yellow at that point is the culprit. An emoticon is worth a dozen words. Link to comment Share on other sites More sharing options...
ToyBoi Posted July 11, 2008 Author Share Posted July 11, 2008 (edited) thanks i tried that and With Selection.QueryTable .Connection = _ "TEXT;" & ImportPath & "\cm" & CStr(number) & "10.txt" .TextFilePlatform = 437 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = True .TextFileTabDelimiter = False .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = True .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _ 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End Withright after it is done with that line, it asks for confirmation Edited August 20, 2008 by ToyBoi Link to comment Share on other sites More sharing options...
Jeemo Posted July 11, 2008 Share Posted July 11, 2008 With Selection.QueryTable .Connection = _ "TEXT;" & ImportPath & "\cm" & CStr(number) & "10.txt" . . . .Refresh BackgroundQuery:=FalseThe problem is the Connection string. The prompt doesn't happen until after the Refresh line above, but because the Connection string doesn't actually gets processed until this line. Try putting your Connection string into a MsgBox within your VBA (just above the With/End With block) to see exactly what gets passed after the concatenation is performed. Here's an example: MsgBox "TEXT;" & ImportPath & "\cm" & CStr(Number) & "10.txt", vbOKOnly, "Connection string" If it doesn't translate to TEXT;C:\[CorrectPath]\[CorrectFileName].txt (which it probably won't), then you'll know where the problem is. Hope this helps, Jeemo An emoticon is worth a dozen words. Link to comment Share on other sites More sharing options...
ToyBoi Posted July 11, 2008 Author Share Posted July 11, 2008 (edited) hi i tried ur suggestion, and it did give me the correct path and file name, that isn't my problem actually. this macro can already point to the right folder and right filename, just needs me to press the import button Edited August 20, 2008 by ToyBoi Link to comment Share on other sites More sharing options...
Jeemo Posted July 12, 2008 Share Posted July 12, 2008 That's strange. I'm not sure what it could be then. If you check out this MSDN help page on the subject, go to the Remarks section and look at the third paragraph - it mentions your exact issue, and indicates that the Connection property is pivotal. An emoticon is worth a dozen words. Link to comment Share on other sites More sharing options...
ToyBoi Posted July 12, 2008 Author Share Posted July 12, 2008 thanks anyways, would you happen to know how i an have excel call an autoit program which would then press the enter key for me? i tried using sendkey with vba but it doesn't work Link to comment Share on other sites More sharing options...
try67 Posted July 13, 2008 Share Posted July 13, 2008 (edited) so this is what i put in there Shell ImportPath & "\Enter.au3" I think you need to either compile the au3 to exe or change the path to point to the AutoIt3 exe and add the script's name as a parameter. That should do the trick. Edited July 13, 2008 by try67 Link to comment Share on other sites More sharing options...
ToyBoi Posted July 17, 2008 Author Share Posted July 17, 2008 I think you need to either compile the au3 to exe or change the path to point to the AutoIt3 exe and add the script's name as a parameter. That should do the trick.thanks it worked, do you know how to let autoit open excel and run a macro in my personal.xls ? 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