VBA & Macro Problems

Prevent Blank Excel Dates from Becoming 1900-01-01 in SQL Server

Huma Ashraf ChHuma Ashraf Ch Nov 22, 2026 868 views

Question details

The user needs blank Excel date cells to be uploaded as NULL values in SQL Server.

Prevent Blank Excel Dates from Becoming 1900-01-01 in SQL Server
Product
Microsoft Excel VBA, ADO, and SQL Server
Device & OS
not provided
Scenario
A VBA macro uploads Excel rows to a SQL Server table whose date column allows NULL.
Observed behavior
Blank date cells are stored as 1900-01-01 rather than SQL NULL.
Before you start

Identify the VBA code that reads the date cell and assigns the ADO parameter, and confirm that the destination column allows NULL. Check whether 1900-01-01 can be a legitimate date before changing existing records.

Solution 1Recommended

Pass NULL for blank Excel dates in the VBA ADO upload

Use this approach to prevent future uploads from converting blank dates into a zero date.

1
Open the upload macro

In Excel, press Alt+F11 and open the module containing the ADO upload. Find the code that reads the date cell and assigns its value to the insert command.

2
Check the cell before assigning the parameter

Test the cell value before converting it to a date. For example, with cell referring to the source cell and dateParam referring to the ADO date parameter, use: If IsError(cell.Value) Then Err.Raise vbObjectError + 1, , "Invalid date cell" ElseIf Len(Trim$(CStr(cell.Value))) = 0 Then dateParam.Value = Null ElseIf IsDate(cell.Value) Then dateParam.Value = CDate(cell.Value) Else Err.Raise vbObjectError + 2, , "Invalid date value" End If

3
Run a test upload

Run the macro with one blank date cell and one valid date cell. In SQL Server, query the inserted rows and confirm that the blank cell produced NULL and the valid date was preserved.

Pass NULL for blank Excel dates in the VBA ADO upload
Use a database NULL: Assign the VBA Null value to the ADO parameter; do not substitute an empty string or zero. The destination column must permit NULL.
Free Microsoft Office alternative

Work with Excel Spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Microsoft Office format compatibility, making it easy to move everyday workbook work across. It does not fix the Excel VBA or SQL Server upload; check macro compatibility before migrating an automated workbook.

Review and edit date cells in a familiar spreadsheet interfaceOpen and work with Microsoft Excel XLSX filesUse a free, lightweight office suiteMove everyday spreadsheet work between compatible Office formats
microsoft office alternative - wps office

Frequently Asked Questions

Why does a blank Excel date become 1900-01-01 in SQL Server?

The upload may convert the blank cell into a zero or empty date value instead of passing a database NULL. Check the VBA code that reads the cell and assigns the ADO parameter.

How do I send a blank date as SQL NULL through ADO?

Before converting the Excel cell to a date, test whether it is blank. If it is, assign the VBA value Null to the ADO date parameter; the SQL Server column must allow NULL.

Can I change existing 1900-01-01 values to NULL?

Yes, if you can identify them as unintended and the column allows NULL. First review matching rows with SELECT, then use UPDATE with a filter that excludes any legitimate 1900-01-01 dates.

Will fixing existing SQL Server rows prevent the problem on the next upload?

No. An UPDATE corrects existing rows only. Modify the VBA upload to pass Null for blank date cells to prevent new occurrences.