Import einer Exceldatei mit „schlechten“ Überschriften
Neulich hatte ich in einem Access-Projekt den Fall, dass regelmäßig auf Knopfdruck eine Exceldatei importiert werden sollte.
An sich kein Problem, dafür gibt es ja
DoCmd.TransferSpreadsheet TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA
Nur hatte die Exceldatei des Kunden sehr „unschöne“ Überschriften:
Teilweise fingen die Überschriften mit einem Leerzeichen, gefolgt von einem Zeilenumbruch an.
Wenn man diese Datei versucht mit DoCmd.TransferSpreadsheet zu importieren, kommt die aussagekräftige Fehlermeldung
Der Suchschlüssel wurde in keinem Datensatz gefunden.
Von welchem Suchschlüssel da auch immer die Rede sein mag …
Erstaunlicherweise hat die TransferSpreadsheet-Methode ja keinen Parameter [SpecificationName ], so wie DoCmd.TransferText, also dass man in einer Spezifikation einfach angeben kann, welche Spalten importiert werden sollen, und wie die Spalten dann in der Zieltabelle heißen sollen.
… Wie so viele Dinge bei Access, die man sich seit Jahren wünscht und nicht begreift, warum sie nicht einfach da sind 🙁
Jetzt kann man natürlich die Überschriften schnell ändern, aber das hilft nicht wirklich, weil der Import ja regelmäßig stattfinden soll.
Man kann auch dem Kunden sagen, „so geht das nicht, sorge dafür, dass die Datei ein vernünftiges Format hat“.
Aber wenn der arme Kunde, das so aus irgendeinem System geliefert bekommt, was soll er machen …
Am einfachsten ist es in diesem Fall, per Code vor dem Import die Überschriften zu ändern.
Hier die entsprechende Prozedur:
Public Sub ExcelFileChangeCaptions(strFilePath As String, arrCaptions As Variant)
' Überschriften in einer Exceldatei ändern
Dim xls As Excel.Application
Dim wb As Workbook
Dim varField As Variant
Dim c As Range
Set xls = New Excel.Application
Set wb = xls.Workbooks.Open(strFilePath)
Set c = wb.Worksheets(1).Cells(1, 1)
For Each varField In arrCaptions
c.Value = varField
Set c = c.Offset(, 1)
Next
wb.Close True
xls.Quit
End Sub
Da steckt nicht viel drin, so dass es keiner Erklärung bedarf.
Hier dann noch ein Beispiel für den Aufruf:
Function ImportDateiAufbereiten(strFile As String) As Boolean
Dim arrCaptions As Variant
On Error GoTo ErrHandle
arrCaptions = Array( _
"VertragID", _
"Vertrag", _
"AdrID", _
"FeldX", _
"Verband", _
"SAPNr", _
"Vertragsbeginn", _
"Vertragsende", _
"Art", _
"VorgangID", _
"Zuschlaege", _
"Ist", _
"Status", _
"etc1", _
"etc2", _
"etc3", _
"etc4")
ExcelFileChangeCaptions strFile, arrCaptions
ImportDateiAufbereiten = True
ExitHere:
Exit Function
ErrHandle:
Select Case err
Case Else
MsgBox err.Description
End Select
GoTo ExitHere
Resume
End Function
Anschließend kann die Datei wie gewohnt mit DoCmd.TransferSpreadsheet importiert werden.
Debug-Trick!
Falls Ihnen das Resume nach dem GoTo ExitHere im Error-Handler aufgefallen ist:
Das halte ich für eine nützliche Sache im Entwicklungsprozess:
Im normalen Programmablauf wird die Resume-Zeile nicht aufgerufen, weil vorher das GoTo ExitHere aufgerufen wird.
Wenn aber die Fehlermeldung angezeigt wird, dann kann man, wenn der Code nicht gesperrt ist, mit Strg+Pause->Debuggen in den Code an die Zeile nach der Messagebox springen.
Normalerweise hat man dann keine Möglichkeit, die Stelle herauszufinden, an der der Fehler auftrat.
Hier kann man jetzt einfach den Cursor in die Resume-Zeile setzen, Strg+F9 drücken -> dadurch ist die Resume-Zeile die nächste asführbare Zeile, und dann führt F8 dazu, dass man in die Zeile springt, in der der Fehler ausgelöst wurde.