Frustrating to say the least…

I have an Excel file that is used to pull data from a database and convert into a DMT journal format for loading.

Using Excel VBA SaveAs the file is saved a XLSX file but DMT complains that the “External Table is not in the expected format”. Weirdly if I do nothing more than open up that saved file and resave (no movement, no editing, no changes), the file will now load via DMT.

I’m trying to put this on automatic daily schedule but this sort of stuff makes it impossible.

Epicor DMT 11.2.300.7 (and 11.2.300.8 - 2023.1.8 Pilot)
Excel for Microsoft 365 MSO (Version 2306 Build 16.0.16529.20100) 64-bit

I’m not sure if this is a DMT or Excel issue. More likely to be Excel I think

Previous topics ( DMT Error - External Table is not in the expected format ) are old and DMT/Epicor and Excel have moved on a bit.

Ideas.

Dim saveDate As String Dim savePath As String saveDate = Format(Application.Range("reportDate"), "YYYYMMDD") shtJnl.Activate shtJnl.copy ActiveWorkbook.SaveAs Filename:="C:\Temp\Daily Journals\Daily_Sales_Journal_Combined_" & saveDate, FileFormat:=xlOpenXMLWorkbook ActiveWorkbook.Close shtGroup.Activate shtGroup.copy ActiveWorkbook.SaveAs Filename:="C:\Temp\Daily Journals\Daily_Sales_Journal_Group_" & saveDate, FileFormat:=xlOpenXMLWorkbook ActiveWorkbook.Close shtLookup.Activate ThisWorkbook.Save End Sub

have also tried

 FileFormat:=xlDefaultWorkbook
with no difference.
Cheers
              
I was going to suggest xlOpenXMLWorkbook but that’s what you got.
xlDefaultWorkbook is whatever your default is.
Try a plain old .xls file → xlWorkbookNormal
              
Cheers Kevin
Application.CalculateUntilAsyncQueriesDone
makes no difference. As it is I tend to refresh queries synchronously as I have other processes that need the data before they can do their thing (and they tend to crash otherwise)
    ThisWorkbook.Connections("Query1").OLEDBConnection.BackgroundQuery = False     ThisWorkbook.Connections("Query").OLEDBConnection.Refresh     ThisWorkbook.Connections("Quert2").OLEDBConnection.BackgroundQuery = False     ThisWorkbook.Connections("Query2").OLEDBConnection.Refresh
Saving a XLS (xlWorkbookNormal) works but I still prefer XLSX (they are smaller files) and a bit easier to fix if things go wrong.
I have had this working before to an XLSX file.
Will look into copying workbook first.
Thanks for your help.
 sbenson:
ThisWorkbook.Connections(“Quert2”).OLEDBConnection.BackgroundQuery = False ThisWorkbook.Connections(“Query2”).OLEDBConnection.Refresh
You see that? Quert2 vs Query2
              
@aosemwengie1 may have a point. I have a terrible time with some XLSX files and DMT because (depending on what you do/how you create it) Excel will ‘hold’ columns and rows in the sheet that DMT can see, but which don’t look like they are part of the dataset on the worksheet.
Converting to CSV will quickly show blank columns and rows as a series of “,” and may lead you to your answer.
              
And XLSX used to work quite regularly.
Given DMT offer XLSX as an acceptable file format it should just work.
I can use XLSX files in other environments with no issues and yet Epicor and DMT seem to be unable to process consistently.
 aosemwengie1:
I’ve been told by support multiple times that csv is the only supported format
Then they need to mark it as experimental or remove it from the program.
If it is programmed to accept excel files, they need to support it.
              
DMT has been using csv, xls and xlsx for a long time. xlsx is a moving target that they do not control and if you just make a simple sheet it will normally work. But as  @MikeGross pointed out Excel will make fields appear empty, but in the background they still are in the dataset. I know if I delete a lot of rows and then do a copy to the bottom Excel will go past the data and copy to the end of the sheet, so that is out of Epicor’s control.
From a support perspective csv is the cleanest since it removes all of the wrapping and if you are having issues then that would be a place to start.
              
These are nothing fancy sheets. No formulas, no formatting, single sheet. I’ve even reset the used range to just the area I want to load.
DMT headers in row 1 and data in the rest.
I would have thought the XLSX would have been easier to handle (unless Epicor is using some weird third party library that itself is buggy) than XLS as XLSX it is an open format. I’ve explored the underlying XML files (worksheet1.xml) and there is nothing there that looks like phantom columns or rows.
Have opened a case with Epico but more to re-raise the issue. The is an old knowledge base article from Aug-19 suggesting just to use CSV.
If they aren’t going to properly support it, then remove it (or at least flag as developmental)