I have a table named horizon that is imported from an excel file. I have automated the process using the "DoCmd.TransferSpreadsheet" function in a macro. There is a column that imports with a Data Type as currency. I have found no way to change that with the DoCmd.TransferSpreadsheet import. So at this point, I have attempted to instead change the column Data type to a number and then from there, change the field property of the column to be a "fixed" format. The code below changes the data type from Currency to Number without a problem, but it does nothing to change the field properties. After running it, I go to Design View and the Data Type has changed, but the Field Properties are still set to Currency.
Code:
'Change the column Data type from "Currency" to "Number
DoCmd.RunSQL "ALTER TABLE Horizon ALTER COLUMN [Cost] Double;"
'Change the column field properties from Currency to Fixed
With db.TableDefs("Horizon").Fields("Cost")
.Properties.Append .CreateProperty("Format", dbText, "Fixed")
.Properties.Append .CreateProperty("DecimalPlaces", dbDouble, 2)
End With
My end goal here is to import the excel spreadsheet and format the columns correctly, and then export it as a csv file - all through a vbs macro.