Loading Excel Data Fail

[anpan]

An Excel file was uploaded every month to collect sales data. However, the person who designed the database left and suddenly the load feature stopped working.
"Sales Load Failed." message window is what I get.
I would appreciate any suggestions about troubleshooting or fixing this issue...

Thank you.

Public Function LoadMonthSales(commdlg As Object) As Boolean    Dim strSQL As String
    Dim strFileName As String
    Dim strpath As String
    Dim strPathFileName As String
    On Error GoTo Load_Error
    
    'strSQL = "INSERT INTO Download ( [ShipTo Customer], [Total Sales], [Profit], [State (s)], [Salesman Code (s)], [Class (s)], [ShipTo Name], Year, Category ) "
    'strSQL = "INSERT INTO Download ( CID, Sales, [GP$], PROV, Salesman, IND, CustomerName, Year, Category ) "
    With commdlg
        .CancelError = True
        .Flags = cdlOFNExplorer + cdlOFNFileMustExist + cdlOFNPathMustExist
        .Filter = "Text Files (*.xls;*.txt)|*.xls;*.txt"
        .FilterIndex = 0
        .DialogTitle = "Load Monthly Sales"
        .ShowOpen
        If MsgBox("Load " & strReportCategory & " sales for " & Format(intReportMonth) & "/" & Format(intReportYear) & " from file " & .FileName & "?", vbOKCancel, "Load Adjustments From Excel") = vbCancel Then Exit Function
        strFileName = .FileTitle
        If InStr(strFileName, "#") > 0 Then
            Mid(strFileName, InStr(strFileName, "#"), 1) = "."
        End If
        strpath = Left(.FileName, Len(.FileName) - Len(.FileTitle) - 1)
        strPathFileName = strpath & "\" & strFileName
        DoCmd.DeleteObject acTable, "tblTest"
        DoCmd.TransferSpreadsheet acImport, , "tblTest", strPathFileName
        'Excel 8.0;DSN=DownloadLinkSpecification;HDR=NO;IMEX=2;DATABASE
        'strSQL = strSQL & " SELECT CID, Sales, [GP$], PROV, Salesman, IND, CustomerName, " & Format(ReportYear()) & " AS Year, """ & strReportCategory & """ AS Category FROM [" & strFileName & "] IN '' [Excel 8.0;HDR=Yes;IMEX=1;DATABASE=" & Left(.FileName, Len(.FileName) - Len(.FileTitle) - 1) & "] "
        'strSQL = strSQL & " SELECT [ShipTo Customer], [Total Sales], [Profit], [State (s)], [Salesman Code (s)], [Class (s)], [ShipTo Name], " & Format(ReportYear()) & " AS Year, """ & strReportCategory & """ AS Category FROM [" & strFileName & "] IN '' [Text;DSN=DownloadLinkSpecification;FMT=Fixed;HDR=NO;IMEX=2;DATABASE=" & Left(.FileName, Len(.FileName) - Len(.FileTitle) - 1) & "] "
        'strSQL = strSQL & " WHERE ((([ShipTo Customer]) Is Not Null) AND (([State (s)]) Is Not Null) AND (([Salesman Code (s)]) Is Not Null) AND (([Class (s)]) Is Not Null) AND (([ShipTo Name]) Is Not Null));"
        'strSQL = strSQL & " WHERE (((CID) Is Not Null) AND ((PROV) Is Not Null) AND ((Salesman) Is Not Null) AND ((IND) Is Not Null) AND ((CustomerName) Is Not Null));"


    End With
    With CurrentDb()
        '.QueryDefs!appqImportSales.SQL = strSQL 'cw old code replaced with transfer sheet above
        .Execute "Delete * FROM Download", dbFailOnError
        '.Execute "appqImportSales", dbFailOnError 'cw old code replaced with transfer sheet above as could not get SQL to import excel
        .Execute "qryDeleteRow1", dbFailOnError 'first row contains file names
        If strReportCategory = "Tools" Then
            .Execute "qryAppendExcelToolsToDownload", dbFailOnError
        Else
            .Execute "qryAppendExcelBearingsToDownload", dbFailOnError
        End If
        '.Execute "Add_MonthlyDownload", dbFailOnError
    End With
    
'cw update Category and Year using DAO
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("Download")
Do While Not rs.EOF
    rs.Edit
    rs!YEAR = ReportYear
    rs!CATEGORY = strReportCategory
    rs.Update
    rs.MoveNext
Loop


With CurrentDb()
    .Execute "Update_MonthlyDownload", dbFailOnError 'cw - links download to tablSales and updates - created by former developer
    .Execute "Add_MonthlyDownload", dbFailOnError 'cw - Updates Sales data - created by former developer
End With


    
    LoadMonthSales = True
    
Load_Exit:
    
    Exit Function


Load_Error:
    MsgBox "Sales Load Failed.", vbExclamation
    Resume Load_Exit
    
End Function

[Micron]

Put a breakpoint at the start of the code, do whatever it is that triggers the code and step thru with F8. Watch your variables (mouse over them or inquire in the immediate window with e.g.
?strFileName << and hit enter on the line you type

Note that a variable that has not been evaluated can't be reliably determined, if at all - you'd have to execute the line (F8) and then check. You can also check line statements that don't pertain to variables; e.g.
?InStr(strFileName, "#") > 0

Using those techniques you should be able to see what line causes the error and perhaps even why.
Alternatively you could copy/compact/zip the file and post it, as long as that includes any back end file for tables if so designed.

[anpan]

Thank you for your response. It works up to .CancelError = True. Next line .Flags = cdlOFNExplorer + cdlOFNFileMustExist + cdlOFNPathMustExist jumps to MsgBox "Sales Load Failed.", vbExclamation

[Micron]

... and the values of the 3 undeclared variables being added all look OK? Perhaps they are declared elsewhere but the values are not correct for the variable type.
Or is the issue on the other side of = , in that whatever those add up to is no good for the .Flags property. BTW, I have no idea what a commdlg is. Looks like a user defined object.

[CJ_London]

change your error message from

MsgBox "Sales Load Failed.", vbExclamation

to


MsgBox "Sales Load Failed." & vbcrlf & err & ": " & err.description, vbExclamation


will give you more information about what the problem actually is

[anpan]

Would it be possible to let me know the best way to check if the values are correct for the variable type? I have no idea what commdlg is either. I'm sorry. I just do not have anybody who is knowledgeable about VBA.

[anpan]

Thank you for your response. MsgBox "Sales Load Failed." & vbcrlf & err & ": " & err.description, vbExclamation gave me 438: Object doesn't support this property or method.

[Welshgasman]

Find out where that function is called and exactly what is being passed to it

[Micron]

I think you're going to have to copy the db, compact/repair that, zip it and post it here. Unless someone knows what that object is there's no way to know what its properties or methods are. If privacy is an issue, remove what isn't necessary to produce the problem and consider randomizing what's left:
https://www.accessforums.net/showthread.php?t=77482

EDIT - Could also try ctrl+F to find commdlg in your code. Make sure the option to search the whole project is selected. The name reads like it is about "common dialog"
#2 - looks like it is an API dialog from Access 2.0 era. If so, time to upgrade this db? It's probably using code from 1996, some of which was probably deprecated long ago.

[Welshgasman]

Dupe post by mistake.

[CJ_London]

Suspect commdlg might be a .dll (common dialog control) which implies an activeX control and/or use of API's and suggests a missing library file. See these links for more info

http://computer-programming-forum.co...e1594f0878.htm
https://docs.microsoft.com/en-us/win...og-box-library


@anpan - things don't normally just 'stop working', something will have changed. It may have been out of your control like a windows update (solution, wind back to an earlier update). Or it may be something you have done such as moved from 32bit to 64bit access (not all 32bit active x controls have a 64b it equivalent), or imported everything into a new db (in which case you need to add the library reference/s). Or perhaps someone has removed the file so the library isn't accessible. Or perhaps the app has become corrupted or maybe you are trying to be run on a different machine. So think back - has something been changed?

One other thing to try is in the VBA editor ensure you have Option Explicit at the top of every module. Once you have done that click on debug>compile. If it doesn't compile, come back with what it fails on.


this is a list of the API's used by the commdlg.dll - see if you are using any of them

line
Declare PtrSafe Function GetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
Declare PtrSafe Function GetSaveFileName Lib "comdlg32.dll" Alias "GetSaveFileNameA" (pOpenfilename As OPENFILENAME) As Long
Declare PtrSafe Function GetFileTitle Lib "comdlg32.dll" Alias "GetFileTitleA" (ByVal lpszFile As String, ByVal lpszTitle As String, ByVal cbBuf As Integer) As Integer
Declare PtrSafe Function ChooseColor Lib "comdlg32.dll" Alias "ChooseColorA" (pChoosecolor As CHOOSECOLOR) As Long
Declare PtrSafe Function FindText Lib "comdlg32.dll" Alias "FindTextA " (pFindreplace As FINDREPLACE) As LongPtr
Declare PtrSafe Function ReplaceText Lib "comdlg32.dll" Alias "ReplaceTextA" (pFindreplace As FINDREPLACE) As LongPtr
Declare PtrSafe Function ChooseFont Lib "comdlg32.dll" Alias "ChooseFontA" (pChoosefont As CHOOSEFONT) As Long
Declare PtrSafe Function PrintDlg Lib "comdlg32.dll" Alias "PrintDlgA" (pPrintdlg As PRINTDLG) As Long
Declare PtrSafe Function CommDlgExtendedError Lib "comdlg32.dll" Alias "CommDlgExtendedError" () As Long
Declare PtrSafe Function PageSetupDlg Lib "comdlg32.dll" Alias "PageSetupDlgA" (pPagesetupdlg As PAGESETUPDLG) As Long

[Micron]

as noted

#2 - looks like it is an API dialog from Access 2.0 era. If so, time to upgrade this db? It's probably using code from 1996, some of which was probably deprecated long ago.

Probably time to upgrade this to a version of msoFileDialog because I agree - some sort of system/architecture change has taken place between the last time this worked and now.