Autoit call Excel Macro

[ToyBoi]

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"

[big_daddy]

What method is the macro calling that causes the conformation.

[Jeemo]

Can you post your Excel VBA code? A quick mod will probably suppress that prompt without having to involve AutoIT.

[ToyBoi]

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 popup

Sub 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

[try67]

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.

[ToyBoi]

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?

[Jeemo]

@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 macro
• Hit Alt+F11 to open the VB Editor
• Browse to the module within Personal.xls that contains the code you pasted
• You 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.

[ToyBoi]

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 With

right after it is done with that line, it asks for confirmation

Edited August 20, 2008 by ToyBoi

[Jeemo]

With Selection.QueryTable

.Connection = _

"TEXT;" & ImportPath & "\cm" & CStr(number) & "10.txt"

.

.

.

.Refresh BackgroundQuery:=False

The 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

[ToyBoi]

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

[Jeemo]

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.

[ToyBoi]

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

[try67]

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

[ToyBoi]

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 ?