Access 2010 - Excel data import issue

K

knarlyd@hotmail.com

Registered User.
Local time
Today, 10:55
Joined
Sep 6, 2013
Messages
43
First off, I'm a complete novice with VBA. I'm using Access 2010.

I have a database that during the import of Excel data, it brings up a dialog box, "Minor loss of Fidelity." I've searched high and low but have found nothing to dismiss that box using VBA. Reg hacks have been tried, disabling that "feature" in Excel 2010 have all failed.

I did however find code referenced in the link below and wonder how/if I can use it to to "watch" for that dialog box during transferimport routine, then maybe use SendKeys to dismiss the box, so the import finishes w/o user intervention. Any code, etc. is of course appreciated!

My import code follows the link.
(rather than repost someone else's code):
http://www.everythingaccess.com/tut...external-application-window-to-the-foreground

Code: 
Private Sub Command0_Click()
On Error GoTo PROC_ERR

Dim strpathtofile As String
Dim strTable As String, strBrowseMsg As String
Dim strFilter As String, strInitialDirectory As String
Dim blnHasFieldNames As Boolean

    blnHasFieldNames = True

    strBrowseMsg = "Select the EXCEL file:"
    strInitialDirectory = ""
    strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", "*.xls")
    strpathtofile = ahtCommonFileOpenSave(InitialDir:=strInitialDirectory, _
    Filter:=strFilter, OpenFile:=False, _
    DialogTitle:=strBrowseMsg, _
    Flags:=ahtOFN_HIDEREADONLY)

    If strpathtofile = "" Then
        MsgBox "No file was selected.", vbOK, "No Selection"
    Exit Sub
    Else

    If Not fSaveExcelFile(strpathtofile) Then
        MsgBox "Unable to save file in correct format", vbOK, "Please check ..."
    Exit Sub
    End If
    End If

    strTable = "SM_Import"
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryDelTblContent"
    DoCmd.SetWarnings True

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, strpathtofile, blnHasFieldNames, "A10:Z200"

    MsgBox "File Imported!", vbInformation, "Import Success"
    DoCmd.OpenReport "rpt_Random_Cases_Generated", acViewPreview

Exit Sub

PROC_ERR:
    MsgBox "Error No.: " & Err.Number & vbNewLine & vbNewLine & _
          "Description: " & Err.Description
           
     MsgBox "Error! " & vbCrLf & "Excel file structure error." &  vbCrLf & "File was NOT imported!", vbExclamation, "IMPORT ERROR"

End Sub
 
If it's the last line where it informs you the import is finished, you really don't need it, just REM it out or remove completely.
 
knarlyd@hotmail.com said:
First off, I'm a complete novice with VBA. I'm using Access 2010.

I have a database that during the import of Excel data, it brings up a dialog box, "Minor loss of Fidelity." I've searched high and low but have found nothing to dismiss that box using VBA. Reg hacks have been tried, disabling that "feature" in Excel 2010 have all failed.

I did however find code referenced in the link below and wonder how/if I can use it to to "watch" for that dialog box during transferimport routine, then maybe use SendKeys to dismiss the box, so the import finishes w/o user intervention. Any code, etc. is of course appreciated!

My import code follows the link.
(rather than repost someone else's code):
http://www.everythingaccess.com/tut...external-application-window-to-the-foreground

Code: 
Private Sub Command0_Click()
On Error GoTo PROC_ERR
 
Dim strpathtofile As String
Dim strTable As String, strBrowseMsg As String
Dim strFilter As String, strInitialDirectory As String
Dim blnHasFieldNames As Boolean
 
    blnHasFieldNames = True
 
    strBrowseMsg = "Select the EXCEL file:"
    strInitialDirectory = ""
    strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", "*.xls")
    strpathtofile = ahtCommonFileOpenSave(InitialDir:=strInitialDirectory, _
    Filter:=strFilter, OpenFile:=False, _
    DialogTitle:=strBrowseMsg, _
    Flags:=ahtOFN_HIDEREADONLY)
 
    If strpathtofile = "" Then
        MsgBox "No file was selected.", vbOK, "No Selection"
    Exit Sub
    Else
 
    If Not fSaveExcelFile(strpathtofile) Then
        MsgBox "Unable to save file in correct format", vbOK, "Please check ..."
    Exit Sub
    End If
    End If
 
    strTable = "SM_Import"
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryDelTblContent"
    DoCmd.SetWarnings True
 
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, strpathtofile, blnHasFieldNames, "A10:Z200"
 
    MsgBox "File Imported!", vbInformation, "Import Success"
    DoCmd.OpenReport "rpt_Random_Cases_Generated", acViewPreview
 
Exit Sub
 
PROC_ERR:
    MsgBox "Error No.: " & Err.Number & vbNewLine & vbNewLine & _
          "Description: " & Err.Description
 
     MsgBox "Error! " & vbCrLf & "Excel file structure error." &  vbCrLf & "File was NOT imported!", vbExclamation, "IMPORT ERROR"
 
End Sub
Click to expand...

The message is caused by the "Compatibility Checker" in Excel (which alerts the user to minor incompatibilities with versions 2003 and earlier. There are two quick soluttions to this problem. You can disable the compatibiliy checker by going (in Excel) to File>Info>Check for Issues> and then Uncheck the Compatibility Checker when it pops up. You can also convert the .xls file to the newer .xlsx format which will resolve the issue. Then just change the suffix of the file in the Access command.

Best,
Jiri
 
Solo712 said:
The message is caused by the "Compatibility Checker" in Excel (which alerts the user to minor incompatibilities with versions 2003 and earlier. There are two quick soluttions to this problem. You can disable the compatibiliy checker by going (in Excel) to File>Info>Check for Issues> and then Uncheck the Compatibility Checker when it pops up. You can also convert the .xls file to the newer .xlsx format which will resolve the issue. Then just change the suffix of the file in the Access command.

Best,
Jiri
Click to expand...

Thanks for the reply, it's appreciated even tho I've already tried those suggestions w/o success. Someone helped out tremendously with some code that will at least import it once I click on that box to never check for compatibility (his code also renames the file which doesn't help as Excel still coughs up that issue). Before having his code, it wouldn't even attempt the import.
Excel still asks each time it sees a new spreadsheet with that special formatting (which is the way it is sent to us) hence the reason that I thought i could use the code I found to bring an application window to the foreground to assist with the issue.
The second biggest issue is that compatibility box warning dialog box sometimes hides behind another window which makes my Access db appear to be locked up in a never ending loop. Once that box is told to override, the spreadsheet successfully imports into my database. But it asks every time during that import routine.:mad:

Since I've spent well over 3 months trying various suggestions to rid that "feature", I'll keep trying to figure if I can use the code I found to bring an application dialog box to the foreground.
 
GinaWhipp said:
If it's the last line where it informs you the import is finished, you really don't need it, just REM it out or remove completely.
Click to expand...

The Access import routine I'm using won't complete the import till that box is acknowledged.
It stops every time during the import (as noted in my second reply to another response below.)
 
knarlyd@hotmail.com said:
Thanks for the reply, it's appreciated even tho I've already tried those suggestions w/o success. Someone helped out tremendously with some code that will at least import it once I click on that box to never check for compatibility (his code also renames the file which doesn't help as Excel still coughs up that issue). Before having his code, it wouldn't even attempt the import.
Excel still asks each time it sees a new spreadsheet with that special formatting (which is the way it is sent to us) hence the reason that I thought i could use the code I found to bring an application window to the foreground to assist with the issue.
The second biggest issue is that compatibility box warning dialog box sometimes hides behind another window which makes my Access db appear to be locked up in a never ending loop. Once that box is told to override, the spreadsheet successfully imports into my database. But it asks every time during that import routine.:mad:

Since I've spent well over 3 months trying various suggestions to rid that "feature", I'll keep trying to figure if I can use the code I found to bring an application dialog box to the foreground.
Click to expand...

I am really baffled that turning off the checker manually does not work. I have never seen that, but hey.... At any rate, you can try to run the module below before the transfer, see if it works for you:

Code: 
Public Sub CCheckerOff(mfile As String)
    Dim xlx As Object, xlw As Object, blnEX As Boolean
    On Error Resume Next
   Set xlx = GetObject(, "Excel.Application")
   If Err.Number <> 0 Then
         Set xlx = CreateObject("Excel.Application")
         blnEX = True
   End If
   Err.Clear
   On Error GoTo 0
   ' Open the workbook
    Set xlw = xlx.Workbooks.Open(mfile, , False) ' opens in RW mode
   ' and close it after disabling the checker
   xlx.DisplayAlerts = False
   xlw.CheckCompatibility = False
   xlw.SaveAs mfile
   xlw.Close False
   xlx.DisplayAlerts = True
 
   Set xlw = Nothing
   If blnEX = True Then xlx.Quit
   Set xlx = Nothing
End Sub

Best of luck,
Jiri
 
Solo712 said:
I am really baffled that turning off the checker manually does not work. I have never seen that, but hey.... At any rate, you can try to run the module below before the transfer, see if it works for you:

Code: 
Public Sub CCheckerOff(mfile As String)
    Dim xlx As Object, xlw As Object, blnEX As Boolean
    On Error Resume Next
   Set xlx = GetObject(, "Excel.Application")
   If Err.Number <> 0 Then
         Set xlx = CreateObject("Excel.Application")
         blnEX = True
   End If
   Err.Clear
   On Error GoTo 0
   ' Open the workbook
    Set xlw = xlx.Workbooks.Open(mfile, , False) ' opens in RW mode
   ' and close it after disabling the checker
   xlx.DisplayAlerts = False
   xlw.CheckCompatibility = False
   xlw.SaveAs mfile
   xlw.Close False
   xlx.DisplayAlerts = True
 
   Set xlw = Nothing
   If blnEX = True Then xlx.Quit
   Set xlx = Nothing
End Sub
Best of luck,
Jiri
Click to expand...


Thank you however I'm not sure how to implement it.:(

I inserted a new module, pasted your code, named it CheckerOff, but can't figure out how to execute it. ;-(

I assume it should run just before the DoCmd.TransferSpreadsheet
Can you enlighten me pls?!
 
knarlyd@hotmail.com said:
Thank you however I'm not sure how to implement it.:(

I inserted a new module, pasted your code, named it CheckerOff, but can't figure out how to execute it. ;-(

I assume it should run just before the DoCmd.TransferSpreadsheet
Can you enlighten me pls?!
Click to expand...

Yes, just type "CheckerOff" on the line before the command statement.
Don't forget the argument, like this:

Code: 
CheckerOff "C:\mypath\myfile.xlsx" 
DoCmd.TransferSpreadsheet.....

Best,
Jiri
 
Solo712 said:
Yes, just type "CheckerOff" on the line before the command statement.
Don't forget the argument, like this:

Code: 
CheckerOff "C:\mypath\myfile.xlsx" 
DoCmd.TransferSpreadsheet.....
Best,
Jiri
Click to expand...

I've uploaded the problem excel spreadsheet that I'm trying to import in Access 2010, as an example as it's not a typical .xls file.

These spreadsheets come to us with an .xls extension however the format they're sending is actually HTML. The code I have is able to import it after I give the "okay" to accept compatibility checker issue.
While your code doesn't error out while it's running, when the DoCmd.TransferSpreadsheet occurs, it comes up with error "External table is not in the expected format"

Here's a redo of my code eliminating the code I'm currently using to perform the upload, using your code only (please don't laugh too loud at it!)

Code: 
Private Sub Command22_Click()
On Error GoTo PROC_ERR
  
 Dim strpathtofile As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean

   blnHasFieldNames = True
   CCheckerOff "c:\Documents\excel\crs_report_1429737271"

   strTable = "SM_Import"
  strpathtofile = "C:\Documents\excel\crs_report_1429737271"

   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, strpathtofile, blnHasFieldNames, "A10:Z200"
  
   MsgBox "File Imported!", vbInformation, "Import Success"
  
 PROC_ERR:
  MsgBox "Error No.: " & Err.Number & vbNewLine & vbNewLine & _
          "Description: " & Err.Description
           
  MsgBox "Error! " & vbCrLf & "Excel file structure error." & vbCrLf & "File was NOT imported!", vbExclamation, "IMPORT ERROR"
  
 End Sub
 

Attachments

knarlyd@hotmail.com said:
I've uploaded the problem excel spreadsheet that I'm trying to import in Access 2010, as an example as it's not a typical .xls file.

These spreadsheets come to us with an .xls extension however the format they're sending is actually HTML. The code I have is able to import it after I give the "okay" to accept compatibility checker issue.
While your code doesn't error out while it's running, when the DoCmd.TransferSpreadsheet occurs, it comes up with error "External table is not in the expected format"

Here's a redo of my code eliminating the code I'm currently using to perform the upload, using your code only (please don't laugh too loud at it!)

Code: 
Private Sub Command22_Click()
On Error GoTo PROC_ERR
 
 Dim strpathtofile As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean
 
   blnHasFieldNames = True
   CCheckerOff "c:\Documents\excel\crs_report_1429737271"
 
   strTable = "SM_Import"
  strpathtofile = "C:\Documents\excel\crs_report_1429737271"
 
   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, strpathtofile, blnHasFieldNames, "A10:Z200"
 
   MsgBox "File Imported!", vbInformation, "Import Success"
 
 PROC_ERR:
  MsgBox "Error No.: " & Err.Number & vbNewLine & vbNewLine & _
          "Description: " & Err.Description
 
  MsgBox "Error! " & vbCrLf & "Excel file structure error." & vbCrLf & "File was NOT imported!", vbExclamation, "IMPORT ERROR"
 
 End Sub
Click to expand...

Well, that changes things dramatically. I wrote the routine believing that the file was in Excel format. If it is sent to you in HTML and the request to ok the compatibility checker comes prior to the Transfer from a conversion tool then the CCheckerOff sub will not help you. Actually I am surprised that my routine did not bomb out because of the file format. Can you send the code of the fsaveExcelFile() function ?

Best,
Jiri
 
Solo712 said:
Well, that changes things dramatically. I wrote the routine believing that the file was in Excel format. If it is sent to you in HTML and the request to ok the compatibility checker comes prior to the Transfer from a conversion tool then the CCheckerOff sub will not help you. Actually I am surprised that my routine did not bomb out because of the file format. Can you send the code of the fsaveExcelFile() function ?

Best,
Jiri
Click to expand...

Sorry for taking so long - we just had massive layoffs (which I survived...)

Anyway, all the (updated) code is below.
After re-examining the code outside the function, I've had to comment out two lines which are pointed out in
Command0_click(), due to it causing the code to error out "Object variable or With block variable not set Function:fsaveExcel, Module: basExcel"

When those two lines are commented out, the routine works fine until the Compatibility box pops up. After selecting to allow it, the file imports w/o issues.

Here's the main code (followed by the function):

Code: 
Private Sub Command0_Click()
On Error GoTo PROC_ERR

Dim strpathtofile As String
Dim strTable As String, strBrowseMsg As String
Dim strFilter As String, strInitialDirectory As String
Dim blnHasFieldNames As Boolean

    blnHasFieldNames = True
    strBrowseMsg = "Select the EXCEL file:"

    ' Set initial start directory dialog box
    strInitialDirectory = ""

    'Set default file type choices
    strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", "*.xls")
    strpathtofile = ahtCommonFileOpenSave(InitialDir:=strInitialDirectory, _
    Filter:=strFilter, OpenFile:=False, _
    DialogTitle:=strBrowseMsg, _
    Flags:=ahtOFN_HIDEREADONLY)

    If strpathtofile = "" Then
        MsgBox "No file was selected.", vbOK, "No Selection"
    Exit Sub
    Else
  
    CCheckerOff strpathtofile

    '**** The next two lines that are commented out are a problem:

    'Name strpathtofile As strpathtofile & "x"
    'strpathtofile = strpathtofile & "x"
  
    If Not fSaveExcelFile(strpathtofile) Then
        MsgBox "Unable to save file in correct format", vbOK, "Please check ..."
    Exit Sub
    End If
    End If

    strTable = "SM_Import"
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryDelTblContent"
    DoCmd.SetWarnings True

    CCheckerOff "C:\users\Myname\documents\excel\crs_report_1429564698.xls"

  DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, strpathtofile, blnHasFieldNames, "A10:Z200"

  MsgBox "File Imported!", vbInformation, "Import Success"

    ' Kill strPathToFile

   Exit Sub

PROC_ERR:
    MsgBox "Error No.: " & Err.Number & vbNewLine & vbNewLine & _
          "Description: " & Err.Description
           
    MsgBox "Error! " & vbCrLf & "Excel file structure error." & vbCrLf & "File was NOT imported!", vbExclamation, "IMPORT ERROR"

End Sub
Here's the function per your request (I did NOT write this but am very thankful for it!):

Code: 
Function fSaveExcelFile(strpathtofile As String) As Boolean
On Error GoTo Err_fSaveExcelFile
  
  Dim objXL As Object, blXLNewInstance As Boolean, _
      objWB As Object, blWBAlreadyOpen As Boolean, _
      strFileName As String

  Const xlWorkbookNormal As Long = -4143, _
        cTEMP As String = ".TMP.xls"
  Const c97_03_format As Long = 56
  
  On Error Resume Next

  Set objXL = GetObject(, "Excel.Application")
  If Err <> 0 Then
    Set objXL = CreateObject("Excel.Application")
    blXLNewInstance = True
    objXL.Visible = False
    Err = 0
  End If
  objXL.ScreenUpdating = False
  
  strFileName = Dir(strpathtofile)
  Set objWB = objXL.Workbooks(strFileName)

  If objWB Is Nothing Then
  If Err <> 0 Then Err = 0
    Set objWB = objXL.Workbooks.Open(strpathtofile)
  Else
    blWBAlreadyOpen = True
  End If
  
  On Error GoTo Err_fSaveExcelFile

  objWB.CheckCompatibility = False
  objWB.SaveAs strpathtofile & cTEMP, c97_03_format
  objWB.Close

  If Len(Dir(strpathtofile & cTEMP)) Then
    Kill strpathtofile
    Name strpathtofile & cTEMP As strpathtofile
    fSaveExcelFile = True
  End If

Exit_fSaveExcelFile:
  On Error Resume Next

  If Not blWBAlreadyOpen Then objWB.Close
  objWB.CheckCompatibility = True
  Set objWB = Nothing
  objXL.ScreenUpdating = True
  If blXLNewInstance Then objXL.Quit
  Set objXL = Nothing
  If Len(Dir(strpathtofile)) Then Kill strpathtofile & cTEMP
  Exit Function

Err_fSaveExcelFile:
  Select Case Err.Number
  Case Else
    MsgBox "Error No.: " & Err.Number & vbNewLine & vbNewLine & _
           "Description: " & Err.Description & vbNewLine & vbNewLine & _
           "Function: fSaveExcelFile" & vbNewLine & _
           IIf(Erl, "Line No: " & Erl & vbNewLine, "") & _
           "Module: basExcel", , "Error: " & Err.Number
  End Select
  Resume Exit_fSaveExcelFile
  
End Function
 
knarlyd@hotmail.com said:
Sorry for taking so long - we just had massive layoffs (which I survived...)

Anyway, all the (updated) code is below.
After re-examining the code outside the function, I've had to comment out two lines which are pointed out in
Command0_click(), due to it causing the code to error out "Object variable or With block variable not set Function:fsaveExcel, Module: basExcel"

When those two lines are commented out, the routine works fine until the Compatibility box pops up. After selecting to allow it, the file imports w/o issues.

Here's the main code (followed by the function):

Code: 
Private Sub Command0_Click()
On Error GoTo PROC_ERR
 
Dim strpathtofile As String
Dim strTable As String, strBrowseMsg As String
Dim strFilter As String, strInitialDirectory As String
Dim blnHasFieldNames As Boolean
 
    blnHasFieldNames = True
    strBrowseMsg = "Select the EXCEL file:"
 
    ' Set initial start directory dialog box
    strInitialDirectory = ""
 
    'Set default file type choices
    strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", "*.xls")
    strpathtofile = ahtCommonFileOpenSave(InitialDir:=strInitialDirectory, _
    Filter:=strFilter, OpenFile:=False, _
    DialogTitle:=strBrowseMsg, _
    Flags:=ahtOFN_HIDEREADONLY)
 
    If strpathtofile = "" Then
        MsgBox "No file was selected.", vbOK, "No Selection"
    Exit Sub
    Else
 
    CCheckerOff strpathtofile
 
    '**** The next two lines that are commented out are a problem:
 
    'Name strpathtofile As strpathtofile & "x"
    'strpathtofile = strpathtofile & "x"
 
    If Not fSaveExcelFile(strpathtofile) Then
        MsgBox "Unable to save file in correct format", vbOK, "Please check ..."
    Exit Sub
    End If
    End If
 
    strTable = "SM_Import"
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryDelTblContent"
    DoCmd.SetWarnings True
 
    CCheckerOff "C:\users\Myname\documents\excel\crs_report_1429564698.xls"
 
  DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, strpathtofile, blnHasFieldNames, "A10:Z200"
 
  MsgBox "File Imported!", vbInformation, "Import Success"
 
    ' Kill strPathToFile
 
   Exit Sub
 
PROC_ERR:
    MsgBox "Error No.: " & Err.Number & vbNewLine & vbNewLine & _
          "Description: " & Err.Description
 
    MsgBox "Error! " & vbCrLf & "Excel file structure error." & vbCrLf & "File was NOT imported!", vbExclamation, "IMPORT ERROR"
 
End Sub
Here's the function per your request (I did NOT write this but am very thankful for it!):

Code: 
Function fSaveExcelFile(strpathtofile As String) As Boolean
On Error GoTo Err_fSaveExcelFile
 
  Dim objXL As Object, blXLNewInstance As Boolean, _
      objWB As Object, blWBAlreadyOpen As Boolean, _
      strFileName As String
 
  Const xlWorkbookNormal As Long = -4143, _
        cTEMP As String = ".TMP.xls"
  Const c97_03_format As Long = 56
 
  On Error Resume Next
 
  Set objXL = GetObject(, "Excel.Application")
  If Err <> 0 Then
    Set objXL = CreateObject("Excel.Application")
    blXLNewInstance = True
    objXL.Visible = False
    Err = 0
  End If
  objXL.ScreenUpdating = False
 
  strFileName = Dir(strpathtofile)
  Set objWB = objXL.Workbooks(strFileName)
 
  If objWB Is Nothing Then
  If Err <> 0 Then Err = 0
    Set objWB = objXL.Workbooks.Open(strpathtofile)
  Else
    blWBAlreadyOpen = True
  End If
 
  On Error GoTo Err_fSaveExcelFile
 
  objWB.CheckCompatibility = False
  objWB.SaveAs strpathtofile & cTEMP, c97_03_format
  objWB.Close
 
  If Len(Dir(strpathtofile & cTEMP)) Then
    Kill strpathtofile
    Name strpathtofile & cTEMP As strpathtofile
    fSaveExcelFile = True
  End If
 
Exit_fSaveExcelFile:
  On Error Resume Next
 
  If Not blWBAlreadyOpen Then objWB.Close
  objWB.CheckCompatibility = True
  Set objWB = Nothing
  objXL.ScreenUpdating = True
  If blXLNewInstance Then objXL.Quit
  Set objXL = Nothing
  If Len(Dir(strpathtofile)) Then Kill strpathtofile & cTEMP
  Exit Function
 
Err_fSaveExcelFile:
  Select Case Err.Number
  Case Else
    MsgBox "Error No.: " & Err.Number & vbNewLine & vbNewLine & _
           "Description: " & Err.Description & vbNewLine & vbNewLine & _
           "Function: fSaveExcelFile" & vbNewLine & _
           IIf(Erl, "Line No: " & Erl & vbNewLine, "") & _
           "Module: basExcel", , "Error: " & Err.Number
  End Select
  Resume Exit_fSaveExcelFile
 
End Function
Click to expand...

You can take CCheckeroff routines out (you put them in twice) completely. The fSaveExcelFile() function already has the line that you need:
Code: 
objWB.CheckCompatibility = False
All I was doing was creating the Excel Wkbk object to force this line through. But it is already done. Maybe the compatibility box pops up because the function resets the CheckCompatibility to True before destroying the object. Try to comment that line out, i.e. :

Code: 
Exit_fSaveExcelFile:
On Error Resume Next
 
If Not blWBAlreadyOpen Then objWB.Close
[COLOR=green]'objWB.CheckCompatibility = True ' <=comment out[/COLOR]
Set objWB = Nothing

See if that helps. I really don't know ! It should work without the box popping up the way it was written up in the fsaveExcelFile. I am at my wits' end.

BTW, the two lines were causing you problems most likely because you were forcing the file into ".xlsx" format. Not a good idea. If you want to save the file in a different format you have let Excel do it. Renaming the file won't do it. I did not find any reference to HTML conversion - in fact there is none, the file is Excel.


Good luck.

Best, Jiri
 
Solo712 said:
You can take CCheckeroff routines out (you put them in twice) completely. The fSaveExcelFile() function already has the line that you need:
Code: 
objWB.CheckCompatibility = False
All I was doing was creating the Excel Wkbk object to force this line through. But it is already done. Maybe the compatibility box pops up because the function resets the CheckCompatibility to True before destroying the object. Try to comment that line out, i.e. :

Code: 
Exit_fSaveExcelFile:
On Error Resume Next
 
If Not blWBAlreadyOpen Then objWB.Close
[COLOR=green]'objWB.CheckCompatibility = True ' <=comment out[/COLOR]
Set objWB = Nothing
See if that helps. I really don't know ! It should work without the box popping up the way it was written up in the fsaveExcelFile. I am at my wits' end.

BTW, the two lines were causing you problems most likely because you were forcing the file into ".xlsx" format. Not a good idea. If you want to save the file in a different format you have let Excel do it. Renaming the file won't do it. I did not find any reference to HTML conversion - in fact there is none, the file is Excel.


Good luck.

Best, Jiri
Click to expand...

Sadly, it didn't make any difference, arg....Oh well, your input is greatly appreciated!:o
 
You must log in or register to reply here.

Similar threads

oxicottin
Solved Import from excel and delete unwanted data
Replies
14
Views
1,772
oxicottin
oxicottin
D
Clearing data in a form after clicking print report
Replies
9
Views
1,258
Ken Sheridan
Ken Sheridan
A
Combining multiple reports into a PDF export
Replies
2
Views
923
KitaYama
K
MsAccessNL
Outlook import csv error
Replies
5
Views
889
MsAccessNL
MsAccessNL
S
Solved Timing or other issue when closing Excel from Access
Replies
13
Views
1,390
sxschech
S

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom