CODEWITHSUNDEEP
shellvbaexcelcommand-linebatch-file
eathapeking
eathapeking

Reputation: 329

Cmd on VBA code error " this object does not support this property or method"

here the code i used i try to run cmdline to create the txt file and then import it into excel it's keep saying " this object does not support this property or method"

i dont know where to fix it i'm stuck now

Sub ImportTextFile()  
Dim rPaht As String
Dim rFileName As String
Dim rPaht1 As String
Dim rFileName1 As String

txtFpath = Sheet1.Range("a1").Value
Filesum = "type unixinv* > summary2.txt"

ChDrive "D"
RSP = Shell(Environ$("COMSPEC"), vbNormalFocus)


Application.Wait Now + TimeValue("00:00:03")
SendKeys "CD " & txtFpath & "{ENTER}", True


Application.Wait Now + TimeValue("00:00:04")
SendKeys Filesum & "{ENTER}", True


Application.Wait Now + TimeValue("00:00:04")
SendKeys "exit " & "{ENTER}", True

rPaht = Sheet1.Range("a1")
rFileName = Sheet1.Range("a2")
Sheet1.Cells.Clear
With Sheet4.QueryTables.Add(Connection:= _
    "TEXT;" & rPaht & "\" & rFileName & ".txt", Destination:=Sheet1.Range("$A$4"))
    .Name = Sheet1.Range("C8").Value
    .TextFilePlatform = 874
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileOtherDelimiter = ":"
    .Refresh BackgroundQuery:=False
End With
Sheet1.Range("a1") = rPaht
Sheet1.Range("a2") = rFileName
End Sub

Upvotes: 0

Views: 871

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149287

Sendkeys are highly unreliable. What you are trying can be easily achieved without sending keys to the command window after opening it. See the below code. It cuts down all excessive code.

in the below example, I am demostrating on how to export DIR to summary2.txt Change as applicable.

Sub ImportTextFile()
    Dim txtFpath As String, Filesum As String
    Dim RSP

    'Ex: txtFpath = "D:\MyFolder"
    txtFpath = Sheet1.Range("a1").Value

    Filesum = "cmd.exe /c Type " & txtFpath & "summary2.txt"

    RSP = Shell(Filesum, vbHide)

    rPaht = Sheet1.Range("a1")
    '
    '~~> Rest of your code
    '
End Sub

Upvotes: 2

Related Questions