Language problème cmd /c dir

[RollyLCXL]

Hi, I used the code ... sn = Split(CreateObject("wscript.shell").exec("cmd /c dir """ & F & """ /a:-d /b").StdOut.ReadAll, vbCrLf). and after it goes on = Application.Transpose(sn). That returned what I need on the range. But ... Some files names in the Windows Files Explorer directory contains caracters such as é, ê, è etc. because the names are in french.

The problem is that in my range such caracters are replaced. As exemple ... Nouveautés was returned by Nouveaut,. My question is how to returned exactly as it is in the Windows File Explorer? Thanks in advance.

 

[Greg Truby]

How married are you to pulling the list of files using wscript? Could you set a reference to the MS Scripting Runtime library and just loop the FILES collection of the FOLDER object under the FILESYSTEMOBJECT? I just ran a test and pulling a list of filenames that included the following characters á, é, ñ, and ö worked just fine. Here's a snippet of the code:

'__________________________________________________
Sub GetFolderInfo(ByRef varFileInfo As Variant, _
                  ByVal strFolder As String, _
                  ByVal booSubFolders As Boolean, _
                  ByVal booListFiles As Boolean)
'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
    Dim fsoFolder As Scripting.Folder, fsoSubFolder As Scripting.Folder
    Dim fsoFile As Scripting.File
    Dim lngFolderCount As Long
    
    On Error GoTo err_GetFolderInfo
    Set fsoFolder = m_fsoSystem.GetFolder(strFolder)
    lngFolderCount = UBound(varFileInfo, 2) + 1
    ReDim Preserve varFileInfo(1 To 2, 1 To lngFolderCount)
    varFileInfo(1, lngFolderCount) = fsoFolder.path & mc_strPathFlag
    varFileInfo(2, lngFolderCount) = fsoFolder.Size
    On Error GoTo 0
    If booListFiles Then
        For Each fsoFile In fsoFolder.Files
            lngFolderCount = UBound(varFileInfo, 2) + 1
            Application.StatusBar = "Items Found: " & lngFolderCount
            ReDim Preserve varFileInfo(1 To 2, 1 To lngFolderCount)
            varFileInfo(1, lngFolderCount) = fsoFile.path
            varFileInfo(2, lngFolderCount) = fsoFile.Size
        Next fsoFile
    End If

 

[RollyLCXL]

Thank you for the answer. The list could be up to 700 files. So loop can take up to 30 or 45 seconds. Compare to 2 seconds with wscrip.

 

[Greg Truby]

Interesting. I just pulled a listing of about 1,000 files in less than ten seconds and the call was recursive in order to search subfolders. I don't have any off-the-shelf WScript to try to see how much faster that would run. I'm sure it would be faster. But 30 to 45 seconds seems pretty long for 700 files (unless it's not a local drive, which I didn't test against).

 

[Tetra201]

Maybe something like this:

Sub Test()
    Dim F As String, X As String
    Dim Obj As Object
    F = "C:\Users\JDoe\Desktop\Test\"
    X = "@@@@@"
    Set Obj = CreateObject("Wscript.Shell").exec("cmd /c dir """ & F & """ /A:-D /B >" & F & X)
    Set Obj = Nothing
    Set Obj = CreateObject("Word.Application")
    With Obj
        .Documents.Open Filename:=F & X, _
            ConfirmConversions:=False, _
            ReadOnly:=True, _
            AddToRecentFiles:=False, _
            Encoding:=437
        .Selection.WholeStory
        .Selection.Copy
        .Documents(X).Close 0
        If .Documents.Count = 0 Then .Quit
    End With
    Set Obj = Nothing
    Kill F & X
    Sheets("Sheet1").Range("A1").Select
    ActiveSheet.PasteSpecial Format:="HTML", _
        Link:=False, _
        DisplayAsIcon:=False, _
        NoHTMLFormatting:=True
End Sub

 

[RollyLCXL]

Maybe something like this:

Sub Test()
    Dim F As String, X As String
    Dim Obj As Object
    F = "C:\Users\JDoe\Desktop\Test\"
    X = "@@@@@"
    Set Obj = CreateObject("Wscript.Shell").exec("cmd /c dir """ & F & """ /A:-D /B >" & F & X)
    Set Obj = Nothing
    Set Obj = CreateObject("Word.Application")
    With Obj
        .Documents.Open Filename:=F & X, _
            ConfirmConversions:=False, _
            ReadOnly:=True, _
            AddToRecentFiles:=False, _
            Encoding:=437
        .Selection.WholeStory
        .Selection.Copy
        .Documents(X).Close 0
        If .Documents.Count = 0 Then .Quit
    End With
    Set Obj = Nothing
    Kill F & X
    Sheets("Sheet1").Range("A1").Select
    ActiveSheet.PasteSpecial Format:="HTML", _
        Link:=False, _
        DisplayAsIcon:=False, _
        NoHTMLFormatting:=True
End Sub

 

[RollyLCXL]

Thank you very much for the suggestions. But I don't fully understand. Why opening files? I was not able to make it work.

In a sheet I have a range (ex. Sheets1).range("A700:A1500")) in wich I need to brings the files of the folder of the active workbook. (Application.Thisworkbook.Path & "\".

I already do this. But I'm trying to do much faster. This code ... sn = Split(CreateObject("wscript.shell").exec("cmd /c dir """ & F & """ /a:-d /b").StdOut.ReadAll, vbCrLf) ... really fast. (1 second)

I tried with ... StrConv("ABCDEFG", vbFromUnicode). I've tried the code ...

sn = StrConv(Split(CreateObject("wscript.shell").exec("cmd /c dir """ & F & """ /a:-d /b").StdOut.ReadAll, vbCrLf) , vbFromUnicode) ... did not worked.

 

[Greg Truby]

Regarding your question on "why opening files" -- I'm assuming you're referring to Tetra's code since my doesn't. He's not opening individual files, he's opening the output file from the call to WScript.Shell.

However, I did need to tweak one line of code from Tetra's post for it to work for me (and it did work correctly).

    Set Obj = CreateObject("Wscript.Shell").exec("cmd /c dir """ & F & """ /A:-D /B >""" & F & X & """")