Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
DIR fn when filename has non-ANSI characters
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
DIR fn when filename has non-ANSI characters
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
DIR fn when filename has non-ANSI characters
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
DIR fn when filename has non-ANSI characters
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" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
DIR fn when filename has non-ANSI characters
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
DIR fn when filename has non-ANSI characters
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
DIR fn when filename has non-ANSI characters
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" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
DIR fn when filename has non-ANSI characters
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" wrote in message ... 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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
DIR fn when filename has non-ANSI characters
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
extract characters from filename | Excel Worksheet Functions | |||
Additional characters in filename | Excel Discussion (Misc queries) | |||
Checking for Invalid filename characters | Excel Programming | |||
Need to convert Characters between Unicode and ANSI | Excel Programming | |||
Wildcard for numeric characters in filename? | Excel Programming |