Excel: Save as csv via vba and manually results in a different file -
i need save several xlsm files csv in order import them in other programs (such r etc.). i've written necessary import routines in other programs , work nicely case 1:
case 1: manually saving xlsm csv if use option , manually save each file csv , click yes on prompts, .csv file looks similar normal excel file when opened again within excel. standard column view, , nothing comma separated etc. (maybe is, doesn't way..)
case 2: saving xlsm vba csv here different file when opened again in excel. 1 looks "real" csv file values being comma separated.
my questions are: 1. why there difference? 2. how can programmatically reach case 2 vba? or impossible?
if 2 impossible have rewrite import code routines handle "normal" csv file...not difficult still lot of work , i'm wondering why there difference..
q1: don't think there difference, @ least not in example pulled together. q2: try out:
i've got 3 example xlsm files in c:\stack\folder1
pictured below:
each file has single data sheet, we'll turn csvs:
i'm sure routine more complicated, test csv output i'm going loop through files , save each xlcsv
:
option explicit sub testcsvoutput() dim databook workbook dim datasheet worksheet dim filepaths(3) string dim fileidx long 'set file paths test filepaths(1) = "c:\stack\folder1\test_file_01.xlsm" filepaths(2) = "c:\stack\folder1\test_file_02.xlsm" filepaths(3) = "c:\stack\folder1\test_file_03.xlsm" 'loop through array , save each file csv application.displayalerts = false fileidx = 1 ubound(filepaths) set databook = workbooks.open(filepaths(fileidx)) set datasheet = databook.activesheet databook.saveas fileformat:=xlcsv '<~~ save step databook.close next fileidx application.displayalerts = true end sub
once script completes, end 3 csv files:
each file comma-delimited when opened in text editor:
Comments
Post a Comment