Importing Data Loses Preview Screen When Using Macro RRS feed

  • Question

  • Good morning, all -

    I have another question on importing data from a .csv file to Excel.

    If I manually import the data using Data|From Text/CSV, I get a pop-up screen that lets me see a preview of the data, giving me the option to cancel or edit the query, or confirm it and proceed with the load. Then, once loaded, a sidebar slides in that gives me the name of the query, how many rows were input and a count of errors, if any (see the image below). If I click on the count of errors, a window will pop open giving me an opportunity to view and then edit the errors.

    Now, to automate the process, I started a macro recording and then went through the process to import the .csv data. As I understand macros, this should have recorded and turned to code whatever process I was recording; so, I figured, I should get the same results, whether I execute the command sequence manually or use the macro.

    But, that's not the case.

    When I run the macro I recorded, there is no preview of the loaded data and no sidebar appears to let me view/edit any errors.

    The code created by recording the macro is;

    ActiveWorkbook.Queries.Add Name:="Import Data", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""c:\Import Data.csv""),[Delimiter="","", Columns=24, Encoding=65001, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Column1"", type text}, {""Column2"", type text}, {""Column3"", type text}, {""Column4"", type text}, {" & _
        """Column5"", type text}, {""Column6"", type text}, {""Column7"", type text}, {""Column8"", type text}, {""Column9"", type text}, {""Column10"", type text}, {""Column11"", type date}, {""Column12"", type text}, {""Column13"", type text}, {""Column14"", type date}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Import Data"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Import 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 = "Import Data"
        .Refresh BackgroundQuery:=False
    End With

    Does anyone know how I can get this code to display the preview screen and then the sidebar such that I can check on any errors in the data? Or, at least, just the sidebar?

    Thanx in advance for any help!

    • Edited by Adam Quark Friday, June 21, 2019 6:36 PM
    Friday, June 21, 2019 4:10 PM


  • OK, found it.

    If I go to the Data tab in the Excel menu and click on Queries & Connections, it brings in the sidebar.

    Well, that solves that problem.

    • Marked as answer by Adam Quark Monday, June 24, 2019 7:26 PM
    Monday, June 24, 2019 7:26 PM