Excel 2010 - VBA code to Write Formatted Numbers to CSV -

i'm working on 5 sheet workbook, button named exportcsv on sheet 5 exports data on sheet 3. more specifically, button runs vba code goes row row , checks first 3 cells data. if of first 3 cells have data, whole row selected. after rows data selected, data written row row csv file (the file semicolon-delimited, however).

the problem i'm having cell formatting being copied over, not. example, values in cells formatted accounting $ formatted correctly, meaning "$12,345,678.90" shows "$12,345,678.90." however, values in cells formatted accounting without $ not being written csv correctly, meaning "12,345,678.90" being written "12345678.9."

below macro in question.

dim plansheet worksheet dim temsheet worksheet  private sub exportcsv_click()     dim integer     dim j integer     dim lcolumn long     dim intresult integer     dim strpath string     on error goto errhandler     set temsheet = worksheets(3)     = 2     while < 1001         j = 1         while j < 4             if not isempty(temsheet.cells(i, j))                 temsheet.select                 lcolumn = temsheet.cells(2, columns.count).end(xltoleft).column                 temsheet.range(temsheet.cells(2, 1), temsheet.cells(i, lcolumn)).select             end if             j = j + 1         loop         = + 1     loop     application.filedialog(msofiledialogfolderpicker).initialfilename = application.activeworkbook.path     application.filedialog(msofiledialogfolderpicker).title = "select path"     application.filedialog(msofiledialogfolderpicker).buttonname = "select path"     intresult = application.filedialog(msofiledialogfolderpicker).show      if intresult <> 0         'dispaly message box         strpath = application.filedialog(msofiledialogfolderpicker).selecteditems(1)     end if      dim x long, ff long, s() string     redim s(1 selection.rows.count)     x = 1 selection.rows.count         s(x) = join(worksheetfunction.transpose(worksheetfunction.transpose(selection.rows(x).value)), ";")     next     ff = freefile     filepath = strpath & "\data" & format(now(), "yyyymmddhhmmss") & ".csv"     open filepath output #ff     print #ff, join(s, vbnewline)     close #ff      errhandler:         ...error handling code omitted  end sub 

i need able copy on exact formatting of cells. converting no-$ cells $ cells won't work because values without $ being used calculation later on in process can handle commas, not $, , can't change code later calculation (proprietary plug-in doing calculation.) also, rows have mixed content, meaning values in row text instead of numbers.

i ended following david zemens' advice , overhauled section for x = 1 selection.rows.count see below.

for x = 1 selection.rows.count     y = 1 selection.columns.count         if y <> selection.columns.count             if isnumeric(temsheet.cells(x + 1, y).value)                 if temsheet.cells(x + 1, y).value = 0                     s(x) = s(x) & ";"                 else                     s(x) = s(x) & replace(temsheet.cells(x + 1, y).text, " ", "") & ";"                 end if             else                 s(x) = s(x) & trim(temsheet.cells(x + 1, y).text) & ";"             end if         else             if isnumeric(temsheet.cells(x + 1, y).value)                 if temsheet.cells(x + 1, y).value <> 0                     s(x) = s(x) & replace(temsheet.cells(x + 1, y).text, " ", "")                 end if             else                 s(x) = s(x) & trim(temsheet.cells(x + 1, y).text)             end if         end if     next next 

some more formatting necessary. goes cell cell, purposefully skipping first row of sheet. .text property of of cells returned empty space before value or between $ , value, had removed. trim removes leading , ending spaces while replace replaces spaces in export.