I want to use the DIR function to extract the names of files in a directory
and write them into a spreadsheet. As the filenames are all in Russian they
are all non-ANSI characters and DIR simply returns a question mark character
for each letter in the file name. Is there a way of getting DIR to read
non-ANSI?

Grateful for advice.

I'm guessing, but I think it really has to do with the operating system and
not Excel. there are regional settings in the registry that determines the
language and Excel rreturns the same values that DOS would return. To help
determine if it is an excel problem or a windows Problem try the following.

Open a DOS window by going to start button - Press Run - In text box enter
exe.cmd and press OK. the enter DIR and your filename. You can change
drives by typing H: and changing folder by typing cd TEMP (you can also use
wildcards like * and ? with cd)

"simonc" wrote:

> I want to use the DIR function to extract the names of files in a directory
> and write them into a spreadsheet. As the filenames are all in Russian they
> are all non-ANSI characters and DIR simply returns a question mark character
> for each letter in the file name. Is there a way of getting DIR to read
> non-ANSI?
>
> Grateful for advice.

A DOS listing in a comand window returns the question mark character as well.
I will have to find some other way to get these file names into a
spreadsheet. I can do it manually, one file at a time, in Explorer if I use
file rename and copy the filename text without actually changing it, but
there are a lot of files...

"Joel" wrote:

> I'm guessing, but I think it really has to do with the operating system and
> not Excel. there are regional settings in the registry that determines the
> language and Excel rreturns the same values that DOS would return. To help
> determine if it is an excel problem or a windows Problem try the following.
>
> Open a DOS window by going to start button - Press Run - In text box enter
> exe.cmd and press OK. the enter DIR and your filename. You can change
> drives by typing H: and changing folder by typing cd TEMP (you can also use
> wildcards like * and ? with cd)
>
> "simonc" wrote:
>
> > I want to use the DIR function to extract the names of files in a directory
> > and write them into a spreadsheet. As the filenames are all in Russian they
> > are all non-ANSI characters and DIR simply returns a question mark character
> > for each letter in the file name. Is there a way of getting DIR to read
> > non-ANSI?
> >
> > Grateful for advice.

Hi Simon

Why do you want to do it with DIR?
Could you not use the File Scripting Object to do it directly through VBA?

Here is some code posted by Jim Cone which should do what you want.

Sub ListAllFilesInFolder()
'Jim Cone - Portland Oregon - USA
Dim strPath As String
Dim oFSO As Object
Dim oFile As Object
Dim oFolder As Object
Dim N As Long

strPath = "C:\Program Files\Microsoft Office\Office11"

N = 1
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder(strPath)
Cells(N, 1).Value = oFolder.Path
N = N + 1
For Each oFile In oFolder.Files
Cells(N, 2).Value = oFile.Name
N = N + 1
Next 'oFile
Set oFSO = Nothing
Set oFile = Nothing
Set oFolder = Nothing
End Sub



--
Regards
Roger Govier

"simonc" <(E-Mail Removed)> wrote in message
news:87807937-DD3F-4403-AEBF-(E-Mail Removed)...
> A DOS listing in a comand window returns the question mark character as
> well.
> I will have to find some other way to get these file names into a
> spreadsheet. I can do it manually, one file at a time, in Explorer if I
> use
> file rename and copy the filename text without actually changing it, but
> there are a lot of files...
>
> "Joel" wrote:
>
>> I'm guessing, but I think it really has to do with the operating system
>> and
>> not Excel. there are regional settings in the registry that determines
>> the
>> language and Excel rreturns the same values that DOS would return. To
>> help
>> determine if it is an excel problem or a windows Problem try the
>> following.
>>
>> Open a DOS window by going to start button - Press Run - In text box
>> enter
>> exe.cmd and press OK. the enter DIR and your filename. You can change
>> drives by typing H: and changing folder by typing cd TEMP (you can also
>> use
>> wildcards like * and ? with cd)
>>
>> "simonc" wrote:
>>
>> > I want to use the DIR function to extract the names of files in a
>> > directory
>> > and write them into a spreadsheet. As the filenames are all in Russian
>> > they
>> > are all non-ANSI characters and DIR simply returns a question mark
>> > character
>> > for each letter in the file name. Is there a way of getting DIR to read
>> > non-ANSI?
>> >
>> > Grateful for advice.

Roger

This looks more promising. Unfortunately some of these files are in folders
which have Russian characters in the name and when I paste the folder name
into strPath it again substitutes question marks for each non-ANSI character.
Is there a way round this?

Could you also tell me how to set the macro so it will recognise folders as
well as files when it is going through the For...Next loop?

Thanks

Simon

"Roger Govier" wrote:

> Hi Simon
>
> Why do you want to do it with DIR?
> Could you not use the File Scripting Object to do it directly through VBA?
>
> Here is some code posted by Jim Cone which should do what you want.
>
> Sub ListAllFilesInFolder()
> 'Jim Cone - Portland Oregon - USA
> Dim strPath As String
> Dim oFSO As Object
> Dim oFile As Object
> Dim oFolder As Object
> Dim N As Long
>
> strPath = "C:\Program Files\Microsoft Office\Office11"
>
> N = 1
> Set oFSO = CreateObject("Scripting.FileSystemObject")
> Set oFolder = oFSO.GetFolder(strPath)
> Cells(N, 1).Value = oFolder.Path
> N = N + 1
> For Each oFile In oFolder.Files
> Cells(N, 2).Value = oFile.Name
> N = N + 1
> Next 'oFile
> Set oFSO = Nothing
> Set oFile = Nothing
> Set oFolder = Nothing
> End Sub
>
>
>
> --
> Regards
> Roger Govier
>

I think you need to change your registry. There was a problem a couple of
weeks ago the somebody in Israel was defaulting to Turkish language. Not
sure which registry seting is wrong but found an Internet explorer setting in
the website below

http://msdn.microsoft.com/en-us/library/ms902920.aspx

"simonc" wrote:

> Roger
>
> This looks more promising. Unfortunately some of these files are in folders
> which have Russian characters in the name and when I paste the folder name
> into strPath it again substitutes question marks for each non-ANSI character.
> Is there a way round this?
>
> Could you also tell me how to set the macro so it will recognise folders as
> well as files when it is going through the For...Next loop?
>
> Thanks
>
> Simon
>
> "Roger Govier" wrote:
>
> > Hi Simon
> >
> > Why do you want to do it with DIR?
> > Could you not use the File Scripting Object to do it directly through VBA?
> >
> > Here is some code posted by Jim Cone which should do what you want.
> >
> > Sub ListAllFilesInFolder()
> > 'Jim Cone - Portland Oregon - USA
> > Dim strPath As String
> > Dim oFSO As Object
> > Dim oFile As Object
> > Dim oFolder As Object
> > Dim N As Long
> >
> > strPath = "C:\Program Files\Microsoft Office\Office11"
> >
> > N = 1
> > Set oFSO = CreateObject("Scripting.FileSystemObject")
> > Set oFolder = oFSO.GetFolder(strPath)
> > Cells(N, 1).Value = oFolder.Path
> > N = N + 1
> > For Each oFile In oFolder.Files
> > Cells(N, 2).Value = oFile.Name
> > N = N + 1
> > Next 'oFile
> > Set oFSO = Nothing
> > Set oFile = Nothing
> > Set oFolder = Nothing
> > End Sub
> >
> >
> >
> > --
> > Regards
> > Roger Govier
> >
>

Hi Simon

Could you not just create a folder called C:\Simon and drag all of the files
to that location?

--
Regards
Roger Govier

"simonc" <(E-Mail Removed)> wrote in message
news:4081D251-D2B7-4DD5-A290-(E-Mail Removed)...
> Roger
>
> This looks more promising. Unfortunately some of these files are in
> folders
> which have Russian characters in the name and when I paste the folder name
> into strPath it again substitutes question marks for each non-ANSI
> character.
> Is there a way round this?
>
> Could you also tell me how to set the macro so it will recognise folders
> as
> well as files when it is going through the For...Next loop?
>
> Thanks
>
> Simon
>
> "Roger Govier" wrote:
>
>> Hi Simon
>>
>> Why do you want to do it with DIR?
>> Could you not use the File Scripting Object to do it directly through
>> VBA?
>>
>> Here is some code posted by Jim Cone which should do what you want.
>>
>> Sub ListAllFilesInFolder()
>> 'Jim Cone - Portland Oregon - USA
>> Dim strPath As String
>> Dim oFSO As Object
>> Dim oFile As Object
>> Dim oFolder As Object
>> Dim N As Long
>>
>> strPath = "C:\Program Files\Microsoft Office\Office11"
>>
>> N = 1
>> Set oFSO = CreateObject("Scripting.FileSystemObject")
>> Set oFolder = oFSO.GetFolder(strPath)
>> Cells(N, 1).Value = oFolder.Path
>> N = N + 1
>> For Each oFile In oFolder.Files
>> Cells(N, 2).Value = oFile.Name
>> N = N + 1
>> Next 'oFile
>> Set oFSO = Nothing
>> Set oFile = Nothing
>> Set oFolder = Nothing
>> End Sub
>>
>>
>>
>> --
>> Regards
>> Roger Govier
>>
>

Hello,
Is there a way to include sub folders? Also, it gives you two lines in
excel, A1 has the dir path and B2 gives you the file name with extention, is
there a way to include them both?
e.g. c:\my clone files\clone to delete\clone1.txt

"Roger Govier" wrote:

> Hi Simon
>
> Why do you want to do it with DIR?
> Could you not use the File Scripting Object to do it directly through VBA?
>
> Here is some code posted by Jim Cone which should do what you want.
>
> Sub ListAllFilesInFolder()
> 'Jim Cone - Portland Oregon - USA
> Dim strPath As String
> Dim oFSO As Object
> Dim oFile As Object
> Dim oFolder As Object
> Dim N As Long
>
> strPath = "C:\Program Files\Microsoft Office\Office11"
>
> N = 1
> Set oFSO = CreateObject("Scripting.FileSystemObject")
> Set oFolder = oFSO.GetFolder(strPath)
> Cells(N, 1).Value = oFolder.Path
> N = N + 1
> For Each oFile In oFolder.Files
> Cells(N, 2).Value = oFile.Name
> N = N + 1
> Next 'oFile
> Set oFSO = Nothing
> Set oFile = Nothing
> Set oFolder = Nothing
> End Sub
>
>
>
> --
> Regards
> Roger Govier
>
> "simonc" <(E-Mail Removed)> wrote in message
> news:87807937-DD3F-4403-AEBF-(E-Mail Removed)...
> > A DOS listing in a comand window returns the question mark character as
> > well.
> > I will have to find some other way to get these file names into a
> > spreadsheet. I can do it manually, one file at a time, in Explorer if I
> > use
> > file rename and copy the filename text without actually changing it, but
> > there are a lot of files...
> >
> > "Joel" wrote:
> >
> >> I'm guessing, but I think it really has to do with the operating system
> >> and
> >> not Excel. there are regional settings in the registry that determines
> >> the
> >> language and Excel rreturns the same values that DOS would return. To
> >> help
> >> determine if it is an excel problem or a windows Problem try the
> >> following.
> >>
> >> Open a DOS window by going to start button - Press Run - In text box
> >> enter
> >> exe.cmd and press OK. the enter DIR and your filename. You can change
> >> drives by typing H: and changing folder by typing cd TEMP (you can also
> >> use
> >> wildcards like * and ? with cd)
> >>
> >> "simonc" wrote:
> >>
> >> > I want to use the DIR function to extract the names of files in a
> >> > directory
> >> > and write them into a spreadsheet. As the filenames are all in Russian
> >> > they
> >> > are all non-ANSI characters and DIR simply returns a question mark
> >> > character
> >> > for each letter in the file name. Is there a way of getting DIR to read
> >> > non-ANSI?
> >> >
> >> > Grateful for advice.
>

Check your earlier thread.

pgarcia wrote:
>
> Hello,
> Is there a way to include sub folders? Also, it gives you two lines in
> excel, A1 has the dir path and B2 gives you the file name with extention, is
> there a way to include them both?
> e.g. c:\my clone files\clone to delete\clone1.txt
>
> "Roger Govier" wrote:
>
> > Hi Simon
> >
> > Why do you want to do it with DIR?
> > Could you not use the File Scripting Object to do it directly through VBA?
> >
> > Here is some code posted by Jim Cone which should do what you want.
> >
> > Sub ListAllFilesInFolder()
> > 'Jim Cone - Portland Oregon - USA
> > Dim strPath As String
> > Dim oFSO As Object
> > Dim oFile As Object
> > Dim oFolder As Object
> > Dim N As Long
> >
> > strPath = "C:\Program Files\Microsoft Office\Office11"
> >
> > N = 1
> > Set oFSO = CreateObject("Scripting.FileSystemObject")
> > Set oFolder = oFSO.GetFolder(strPath)
> > Cells(N, 1).Value = oFolder.Path
> > N = N + 1
> > For Each oFile In oFolder.Files
> > Cells(N, 2).Value = oFile.Name
> > N = N + 1
> > Next 'oFile
> > Set oFSO = Nothing
> > Set oFile = Nothing
> > Set oFolder = Nothing
> > End Sub
> >
> >
> >
> > --
> > Regards
> > Roger Govier
> >
> > "simonc" <(E-Mail Removed)> wrote in message
> > news:87807937-DD3F-4403-AEBF-(E-Mail Removed)...
> > > A DOS listing in a comand window returns the question mark character as
> > > well.
> > > I will have to find some other way to get these file names into a
> > > spreadsheet. I can do it manually, one file at a time, in Explorer if I
> > > use
> > > file rename and copy the filename text without actually changing it, but
> > > there are a lot of files...
> > >
> > > "Joel" wrote:
> > >
> > >> I'm guessing, but I think it really has to do with the operating system
> > >> and
> > >> not Excel. there are regional settings in the registry that determines
> > >> the
> > >> language and Excel rreturns the same values that DOS would return. To
> > >> help
> > >> determine if it is an excel problem or a windows Problem try the
> > >> following.
> > >>
> > >> Open a DOS window by going to start button - Press Run - In text box
> > >> enter
> > >> exe.cmd and press OK. the enter DIR and your filename. You can change
> > >> drives by typing H: and changing folder by typing cd TEMP (you can also
> > >> use
> > >> wildcards like * and ? with cd)
> > >>
> > >> "simonc" wrote:
> > >>
> > >> > I want to use the DIR function to extract the names of files in a
> > >> > directory
> > >> > and write them into a spreadsheet. As the filenames are all in Russian
> > >> > they
> > >> > are all non-ANSI characters and DIR simply returns a question mark
> > >> > character
> > >> > for each letter in the file name. Is there a way of getting DIR to read
> > >> > non-ANSI?
> > >> >
> > >> > Grateful for advice.
> >

--

Dave Peterson