2

Intro: I'm writing a VBA macro which imports data from a source, then modifies it in the MS Excel file before putting it in the right order in the first sheet called WegschrijfSheet. This sheet is then copied to a new MS Excel file before the first sheet from this file. (This part works fine.)

Main Question:

Now I'd like to save this file as a CSV. My code does this but not yet in the way I would like it to. Text and/or values which first were in A1, B1, C1, H1 etc. are merged into Cell A1. It does this for all the rows. Empty rows are filled with , signs. Example of the result:

Productieorder,228767,,,,,,,,,,

Artikelnummer,4022 631 79951,,,,,,,,,,

Bewerkingsnummer,14,,,,,,,,,,

Bewerkingsrevisie,1,,,,,,,,,Bestandslocatie ophalen :,Z:\Parts Manufacturing\5. Kwaliteit\130 - in proces meten\macro voor oude excel bestanden omzetten\Testbestanden

(At each , it should jump to the next cell.)

One way to solve this would be changing my PC settings from European to American. Where . is the decimal delimiter and vice versa, but ending up with values for which the decimals are defined by a . would make me unable to use the files for software I use to analyse the data.

Could I somehow use another delimiter than , to solve this?

I know how to select another delimiter manually but have no clue how to do this in VBA. Or better still, is there another solution?

(P.S. this macro has to convert over at least 600 MS Excel files, and counting. So converting all files manually is not an option.)

Here is my code :

ActiveWorkbook.SaveAs FileName:= _
    "Z:\Parts Manufacturing\5. Kwaliteit\130 - in proces meten\macro voor oude excel bestanden omzetten\Wegschrijflocatie\4022 631 79951#14#" & Sheet1.Range("B1").Value & ".csv", _
    Fileformat:=xlCSV, CreateBackup:=False
Community
  • 1
  • 1
Cornelis
  • 445
  • 3
  • 11
  • 27
  • 1
    Please add you existing code and try to be clearer (it is long and not clear) on what this doesn't do that you are trying to implement. And try to find a more evocative title because that doesn't seem to be your real question (to save as CSV) – R3uK Oct 16 '15 at 09:15
  • this is the part of the code for saving as CSV file_______________________: ActiveWorkbook.SaveAs FileName:="Z:\Parts Manufacturing\5. Kwaliteit\130 - in proces meten\macro voor oude excel bestanden omzetten\Wegschrijflocatie\4022 631 79951#14#" & Sheet1.Range("B1").Value & ".csv", Fileformat:=xlCSV, CreateBackup:=False – Cornelis Oct 16 '15 at 09:17
  • Don't put it here, edit[http://stackoverflow.com/posts/33166482/edit] your post to include the code and use the button `{}` to use proper display for you code. I included your saving code, but I guess this isn't the only thing you have for that project, try to show that you have already done an extensive work on this ;) – R3uK Oct 16 '15 at 09:19
  • My goal is to save an Excel file as CSV. It does... but the values&text in the Excel file don't remain in place(al merged in Colomn A) and there are added a lot of ","signs – Cornelis Oct 16 '15 at 09:21
  • 2
    Look in this post. Seems similar to your problem. [use comma instead of dot for decimals](http://stackoverflow.com/questions/22804699/use-comma-instead-of-dot-for-decimals-when-saving-as-text). Seems like you should set Local:=True and specify your decimal character. – vanao veneri Oct 16 '15 at 09:24
  • @vanao veneri sounds good... I'm going to read and try to apply it right now. Thanks so far! – Cornelis Oct 16 '15 at 10:42
  • It works perfectly!! thanks a lot... i was stuck on this issue for weeks. – Cornelis Oct 16 '15 at 10:49

0 Answers0