CODEWITHSUNDEEP
excelimport
Pythonista anonymous
Pythonista anonymous

Reputation: 8950

From Excel to Access: zeros formatted as dashes cause import errors

I am trying to import some tables from Excel 2010 (.xlsx) to Access 2010. I noticed that if a column has zeros formatted as dashes (Excel's "accounting" format, which can also be obtained pressing the comma button on the toolbar), and I import the column as double, then Access yields an import error. If instead I format the column to 'number' in Excel, so that zeros appear as zeros, Access imports the data with no errors. Whi is this? Is there a way to fix this, other than manually reformatting every column in Excel? Thanks!

Upvotes: 0

Views: 142

Answers (1)

KFichter
KFichter

Reputation: 783

I'm not sure exactly what's causing the issue, but this excel macro will run through each worksheet from the first column to the last used column and will change the format of any "accounting" column to a "number" column. I hope it helps.

Sub AccountingReformat()
Dim ws As Worksheet
Dim rng As Range
Dim lastCell As Range

For Each ws In ThisWorkbook.Worksheets
    ws.Activate
    Set lastCell = ws.Cells.Find(What:="*", After:=ws.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
                        xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False)
    For Each rng In Range("A1:" & lastCell.Address).Columns
        If (rng.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)") Then
            rng.NumberFormat = "0.00"
        End If
    Next rng
Next ws
End Sub

Upvotes: 1

Related Questions