Excel to Tab delimited .text

[Former Member]

At work i have to deal with a lot of excels in which i have to save the file as .txt and then .xls, I will then upload the .txt file to SAP

In the txt files, the numbers are being saved with "." (dot) and not with a ","(Comma) in the decimals, even though then numbers in excel are formated in number with 2 decimals and with comma separator.

So I'm having problems with this, I've tried the different formats (number, text, general) but nothing seems to work. If I save manually in Tab delimited .txt the files is always saved with a comma in the decimals, but if i run the excel macro the .txt always save with a dot. I've also tried changing the file format in my VBA (TextWindows, TextMSDOS, Currentplatformtext) without sucess.

Can anyone give me some help with this? I've been looking the internet for a solution but found none. The number of files i have to upload is huge, so i'd like to program my macro to do all this saving automatically.

Thank you..

PS:The VBA code I am using now is this:

Sub Save()



    Dim NomeFicheiro As String
    
    Dim PathGrav As String
        
    NomeFicheiro = InputBox("Nome do Ficheiro?")
    
    PathGrav = "H:\Starflows\" & NomeFicheiro
    


    If NomeFicheiro = "" Then Exit Sub
    

    ActiveWorkbook.SaveAs Filename:=PathGrav, FileFormat:=xlTextWindows, CreateBackup:=False
        
    ActiveWorkbook.SaveAs Filename:=PathGrav, FileFormat:=xlNormal, CreateBackup:=False

  
    ActiveWorkbook.Close SaveChanges:=False

End Sub

[markus_doehr2]

> In the txt files, the numbers are being saved with "." (dot) and not with a ","(Comma) in the decimals, even though then numbers in excel are formated in number with 2 decimals and with comma separator.

I would check your Windows regional/language settings.

Markus

[Former Member]

I thought it might me the regional settings, mine are in Portuguese and by default we have the comma as decimal separator, so this should be ok.

I tried changing to the English (US) format using both the comma and the dot as separator, but the problem remains, the macro is always saved with dot instead of comma, even so if I save the file manually as tab delimited text.

What I also did was, I configured dot as decimal separator in the regional settings and putted commas in the numbers in my excel, I think it recognizes the numbers as text, in this case, when i run the macro the numbers in the text are saved with quotes like this but with commas, for example "12,56". But in this case SAP does not accept the file because of the quotes.

I remembered something that might be the reason for this happening. when I manually save as tab delimited .txt, excel asks me 2 questions, which i answer yes and which could be important.

1. Excel tells me that the selected file type does not support workbooks that contain multiple sheets and to save only the active sheet click ok. This does not look like the problem but i changed the code to:

ActiveSheet.SaveAs Filename:=PathGrav, FileFormat:=xlTextWindows, CreateBackup:=False

I executed it but the dot and not comma remains.

2. 123.txt may contain features that are not compatible with Text (Tab delimited). Do you want to keep the workbook in this format?

*To keep this format, which leaves out any incompatible features, click Yes.

*To preserve the features, click No. then save a copy in the latest Excel format

I press yes and the file is saved as comma delimited.

Could it be that i need to add something to my VBA code, as when the macro runs I am not prompted any of these questions.

Thanks

[markus_doehr2]

I understand your problem but this is the wrong forum.

Your problem is basically not a "SAP Problem", especially not a Problem running "SAP on Windows".

I suggest you check a Microsoft or developer forum, e. g. http://social.technet.microsoft.com/

Markus