Code Examples for Importing / Exporting With EXCEL Workbooks

Ken Snell, Access MVP, has published several code samples - how to import and export to/from Excel. Cool staff, check it out.

GetTempFileName, MAX_PATH and Vista

If you need to create a temporary file name – you normally use GetTempFileNameA API function, like I do also. Here a sample: GetTempFile - Create a temporary file. I noticed that on my Vista PC it sometimes fails with Overflow error message. On other Vista PC it fails all the time. I have no idea why it fails sometimes only, but I found how to fix it - MAX_PATH constant should be set to 32768 - this is because NTFS file path limit is not 255 characters, but 32768. So, do not forget to update your source code if you are using this function.

Update: Recently I found that increasing MAX_PATH does not always help, I am still getting Error #6: “Overflow” sometimes on some PCs. I think the reason that too many files in Temp folder, file is actually generated, even when error occurs. So I just had to use On Error Resume Next for a while…

Getting Run-time errors descriptions

This is from the post of Dirk Goldgar, Access MVP, I saw the question in newsgroups, but forgot about this AccessError() function. Keeping it here. It only works on English Access, so for localized version you have to adjust it.

Dim strErrDesc As String
For i = 1 To 65535
   strErrDesc = Error(i)
  If strErrDesc = "Application-defined or object-defined error" Then
      strErrDesc = AccessError(i)
  End If
   Print #1, i & ", " & strErrDesc
Next i

Export to Excel - There are too many rows to output

I was just hit by Run-time error 2306: “There are too many rows to output, based on the limitation specified by the output format or by Microsoft Access”, produced by this line:

DoCmd.OutputTo acOutputQuery, "MyQuery", acFormatXLS, , True

Query has about 18000 records, what should not be a problem for Excel. Fortunately I found that replacing acFormatXLS with acSpreadsheetTypeExcel9 did the trick, so this line would work:

DoCmd.OutputTo acOutputQuery, "MyQuery", acSpreadsheetTypeExcel9, , True

Interesting that using acSpreadsheetTypeExcel9 is not documented in online help or object browser, and both constants has quite different values:

Const acSpreadsheetTypeExcel9 = 8
Const acFormatXLS = "Microsoft Excel (*.xls)"

Anyway – it works! If anybody have explanation to this – would be great to learn.

BTW - DoCmd.TransferSpreadsheet will also work in this case.