How to change the data type in excel spreadsheet from Access vba?

[sabavno]

Hi,

I want to do the following:

Before I import the excel spreadsheet into Access table, I want to be able to access that spreadsheet and change the data type of some columns in it. Since I don't want my user do it manually, I am looking for the VBA code to solve this problem.

Please advise.

Thanks.

 

[GHolden]

This example open an excel file (C:\Test\Test.xls) and changes the format of column B to dd/mmm/yyyy, closes and saves the file.

Private Sub mChangeExcelFormat()

Dim ExcelApp As New Excel.Application
With ExcelApp

.Workbooks.Open "c:\test\test.xls"
.Visible = True
.Columns("B&quot.NumberFormat = "dd/mmm/yyyy"
.ActiveWorkbook.Close True
.Quit

End With

Set ExcelApp = Nothing

End Sub

Hopefully this will provide a good starting point for what you need to do.

You will need a reference to Microsoft Excel object library

There are two ways to write error-free programs; only the third one works.