none
queryTable.refresh causing issue in /xl/connections.xml RRS feed

  • Question

  • Iwith VBA I am loading data and using queryTable.refresh at the end to load the data into a worksheet. Right on refresh the /xl/connections.xml file is created. When I save the qryTableTest.xlsm and close it, on opening I get this message:

    We found a problem with some content in "qryTableTest.xlsm". Do you want us to try to recover as much as we can?....

    Then I clik on Yes and a message pops up that the error was found in the /xl/connections.xml.

    I want to save the file, but am brought to SAVE AS on the File Menu. Once I save the file under a new name, it is totally corrupt and cannot be opened anymore.

    I have test this behaviour on two machines, bowh Windows 10 Home Edition 64-bit System. I am using Office365.

    I feel that there is a bug in Excel with respect to handling/saving connections. Anyone facing similar issues? Having solutions?

    Thanks

    Friday, January 19, 2018 1:30 PM

Answers

  • Hi mazarata,

    I try to test your code on my side by creating a .CSV file.

    I try to run the code.

    Data inserted correctly in Excel.

    I Save the File and close it.

    I successfully Reopen it without any error.

    I made this Test with Windows10 , Excel 2016.

    So it looks like there is no issue with the code, But issue with the ODBC.

    I find that this error can be caused by several reasons.

    Like connection issue, problem in query, invalid parameter, refresh issue.

    I suggest you to try to run code below and check whether it run successfully or not.

    I try to generate a macro using Excel.

    Sub Macro1()
    '
    ' Macro1 Macro
    '
    
    '
        ActiveWorkbook.Queries.Add Name:="data", Formula:= _
            "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""C:\Users\v-padee\Desktop\data.csv""),[Delimiter="";"", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""Name"", type text}, {""Number"", Int64.Type}})" & Chr(13) & "" & _
            "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
        ActiveWorkbook.Worksheets.Add
        With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
            "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=data;Extended Properties=""""" _
            , Destination:=Range("$A$1")).QueryTable
            .CommandType = xlCmdSql
            .CommandText = Array("SELECT * FROM [data]")
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .ListObject.DisplayName = "data"
            .Refresh BackgroundQuery:=False
        End With
    End Sub
    

    You can also try to manually fetch the record in Excel and try to record macro on your side.

    Then try to check whether it run successfully then you can modify it and use it in future.

    Let us know , IF issue is persist.

    We will try to provide further suggestions to solve the issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by mazarata Thursday, January 25, 2018 12:59 PM
    Thursday, January 25, 2018 1:37 AM
    Moderator

All replies

  • Hi mazarata,

    I try to follow the steps and try to reproduce the issue.

    I try to load the data in Excel using queryTable.refresh.

    Code:

    Sub demo()
       
        Dim sConn As String
        Dim sSQL As String
        Dim oQt As QueryTable
        Dim sh As Worksheet
       
        sConn = "ODBC;DSN=Excel Files;DBQ=C:\Users\v-padee\Desktop\tbl_data.xlsx;"
       
        sSQL = "SELECT Name, Number FROM TheData WHERE Number >=2 ORDER BY Name DESC"
       
        Set sh = ThisWorkbook.Worksheets.Add
       
        Set oQt = sh.QueryTables.Add(sConn, sh.Range("A1"), sSQL)
       
        oQt.Refresh
       
    End Sub
    
     

    When i run the code i got data like below.

    I try to Save the file and close it.

    I reopen the file.

    File get reopened without any error.

    I also Right click on it and click on Refresh and save it and close it. it get reopened successfully. 

    I made this test on Excel 2016 Office 365 version below.

    Let me know, If i missed any steps or provide your code, Excel file with sample data and steps, If your steps are different then mine.

    I will again try to make a test and try to reproduce the issue on my side.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 22, 2018 7:23 AM
    Moderator
  • Dear Deepak

    Thanks very much for your reply and the prrposed solution. I have tried your code and here I run into a different error. On Query.refresh I get an Error Message saying "General ODBC Error". I am surprised, since ODBC Drivers are all installed properly. But let us skip this new issue for a moment and have a look at my original problem, if you don't mind.

    I have a little csv File with your example data:

    Name;Number
    tom; 3
    miki; 5
    jones; 2
    jerry; 4

    And following sub

    Sub QueryTableTest()

        Dim strPath As String

        Dim qryTable As QueryTable

        Dim sh as Worksheet

      

        strPath = ThisWorkbook.Path & "\TestSource.csv"

        Set sh = ThisWorkbook.Worksheets.Add

     
        Set qryTable = sh.QueryTables.Add( _
                        Connection:="TEXT;" & strPath, _
                        Destination:=sh.Range("A1"))
        
        
        With qryTable
            .TextFilePromptOnRefresh = False
            .RefreshStyle = xlInsertDeleteCells
            .TextFileParseType = xlDelimited
            .TextFileSemicolonDelimiter = True
            .Refresh
        End With

    End Sub

    The sheet is refreshed with the correct data from source file. I can save the xlsm-File and close it.

    Yet still, after I open the File I get the same error message.

    I am using Windows 10 Home Edition.

    Thanks, Giuseppe


    • Edited by mazarata Wednesday, January 24, 2018 12:37 PM
    Wednesday, January 24, 2018 12:33 PM
  • Hi mazarata,

    I try to test your code on my side by creating a .CSV file.

    I try to run the code.

    Data inserted correctly in Excel.

    I Save the File and close it.

    I successfully Reopen it without any error.

    I made this Test with Windows10 , Excel 2016.

    So it looks like there is no issue with the code, But issue with the ODBC.

    I find that this error can be caused by several reasons.

    Like connection issue, problem in query, invalid parameter, refresh issue.

    I suggest you to try to run code below and check whether it run successfully or not.

    I try to generate a macro using Excel.

    Sub Macro1()
    '
    ' Macro1 Macro
    '
    
    '
        ActiveWorkbook.Queries.Add Name:="data", Formula:= _
            "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""C:\Users\v-padee\Desktop\data.csv""),[Delimiter="";"", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""Name"", type text}, {""Number"", Int64.Type}})" & Chr(13) & "" & _
            "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
        ActiveWorkbook.Worksheets.Add
        With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
            "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=data;Extended Properties=""""" _
            , Destination:=Range("$A$1")).QueryTable
            .CommandType = xlCmdSql
            .CommandText = Array("SELECT * FROM [data]")
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .ListObject.DisplayName = "data"
            .Refresh BackgroundQuery:=False
        End With
    End Sub
    

    You can also try to manually fetch the record in Excel and try to record macro on your side.

    Then try to check whether it run successfully then you can modify it and use it in future.

    Let us know , IF issue is persist.

    We will try to provide further suggestions to solve the issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by mazarata Thursday, January 25, 2018 12:59 PM
    Thursday, January 25, 2018 1:37 AM
    Moderator
  • Thank you Deepak, your solution perfectly works for me.

    Cheers, mazarata

    Thursday, January 25, 2018 12:59 PM