CODEWITHSUNDEEP
excelvba
vipin k.
vipin k.

Reputation: 2715

Error 1004 in my Macro while saving a Excel workbook as a CSV for Japanese

I have written a simple Macro for saving all the Workbooks as separate CSV files. This works fine on my local machine (English Lang) for paths like *D:\MyFolder* .

But when I am trying the same Macro on another windows machine with Japanese language enabled I am getting 1004 error for SaveAS method.

File paths like D:¥MyFolder¥

Below is the my code which is causing the error:

pathSeperator = Application.PathSeparator

strPath = InputBox("Enter EXISTING Directory path like d:\someDirectoryName, d:", , , 1000)

SaveToDirectory = strPath & pathSeperator & "csv" & pathSeperator
If Dir(strPath & pathSeperator & "csv", vbDirectory) = "" Then

fso.CreateFolder SaveToDirectory
Else

fso.DeleteFolder strPath & pathSeperator & "csv"
fso.CreateFolder SaveToDirectory

End If

For Each WS In ThisWorkbook.Worksheets
    newName = WS.Name & "-" & Format(Date, "yyyy-mm-dd") & "-" & Format(Time, "hhmmss")
    WS.Copy
    ActiveWorkbook.SaveAs SaveToDirectory & newName, xlCSVMSDOS, Local:=True
    ActiveWorkbook.Close Savechanges:=False
Next

Upvotes: 1

Views: 966

Answers (2)

Graham Anderson
Graham Anderson

Reputation: 1239

On the Japanese language machine have you tried changing the font on the visual basic editor to Japanese fonts?

This can be done from the tool->options->format tab.

Edit 22/08/13

A bit of a longshot, but I've read that the Japanese Yen character in ASCII is the same as the / charcter on english language machines, as such using Chr(92) should work in both. On an English language machines it would appear as / whislt on a Japanese machine it would have the yen symbol. A simple test would be to run the following macro on a Japanese machine and see what happens.

Sub TestSeperator()

MsgBox Chr(92)

End Sub

If this is the case then you need to make changes like the ones below:

SaveToDirectory = strPath & Chr(92) & "csv" & Chr(92)
If Dir(strPath & Chr(92) & "csv", vbDirectory) = "" Then

fso.CreateFolder SaveToDirectory
Else

fso.DeleteFolder strPath & chr(92) & "csv"
fso.CreateFolder SaveToDirectory

Upvotes: 1

Graham Anderson
Graham Anderson

Reputation: 1239

I have tried out your code on my English language machine and managed to raise a 1004 error when I entered the directory path including the final "\"

I have modified the code so that it adds the path seperator if it is not present and the rest of the code assumes it is already in strPath.

pathSeperator = Application.PathSeparator

strPath = InputBox("Enter EXISTING Directory path like d:\someDirectoryName, d:", , , 1000)
Set fso = New FileSystemObject

If Right(strPath, 1) <> pathSeperator Then 'added if clause
    strPath = strPath & pathSeperator
End If

SaveToDirectory = strPath & "csv" & pathSeperator  'Removed one pathSeperator
If Dir(strPath & pathSeperator & "csv", vbDirectory) = "" Then

fso.CreateFolder SaveToDirectory
Else

fso.DeleteFolder strPath & "csv" 'Removed one pathSeperator
fso.CreateFolder SaveToDirectory

End If

For Each WS In ThisWorkbook.Worksheets
    newName = WS.Name & "-" & Format(Date, "yyyy-mm-dd") & "-" & Format(Time, "hhmmss")
    WS.Copy
    ActiveWorkbook.SaveAs SaveToDirectory & newName, xlCSVMSDOS, Local:=True
    ActiveWorkbook.Close Savechanges:=False
Next

Upvotes: 0

Related Questions