I cannot find the previous question I asked about this. Someone provided a process by which I could change the delimeter for Excel "CSV" files from a comma to the pipe character. I need this because some of the text fields contain embedded commas and I don't want to change those, but I need the data in a CSV format for uploading using the SQL Server Bulk Insert command.
At any rate, when I open the Excel spreadsheet manually and save the file as a CSV, (also manually), I get the pipe delimiter. But if I open the spreadsheet via Access automation and save the worksheet:
osht.SaveAs Replace(oWbk.FullName, ".xlsx", ".csv"), xlCSV
I get a comma separated file instead of pipe separated. Can anyone provide any recommendations which will allow me to automate saving the selected sheet of the XL file as a pipe delimited file?