Databases
StackOverflow2013
Postsdbo
POExcel: Name Table after Import from CSV - running into issues
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POExcel: Name Table after Import from CSV - running into issues
    text
    copied!<p>I have been working on a workbook for some reporting functions. </p> <p>I have just been getting back into VB and a bit rusty - in front of me also sits a 1000+ page book and have been all over sites like this. It seems that i can get close - however end up just a bit off. </p> <p>Every week I have new data coming in, that I will be using to update some pivot tables and so on. I have 5 areas where data will be imported and currently have the import functioning, however I run into issues with two areas: I can't seem to have it create a table of the data that was just imported &amp; I have no error checking. I need the tables named so i can use the date throughout the worksheet.</p> <p>Any guidance would be great for the following: </p> <ul> <li><p>How can I simply clear out the current table at A2 and import the new data not to have to rename the table and the headers? (my imported data comes in as "First Name" not First_Name" so would be nice to just keep it in this format. </p></li> <li><p>How do I add error checking on this to say if someone runs it and closes the window that it will not clear the worksheet and launch a debugger?</p></li> </ul> <p>Here is what i have working (I have tried many things but back to this)</p> <pre><code>Sub ImportMaster() ' this is the master list of all NA accounts for ..... ' NA_ACCOUNTS_LIST Worksheet Dim ws As Worksheet, strFile As String Set ws = ActiveWorkbook.Sheets("NA_Accounts_List") ws.UsedRange.Clear strFile = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Please selec text file...") With ws.QueryTables.Add(Connection:="TEXT;" &amp; strFile, _ Destination:=ws.Range("A2")) .Name = "ProgramData" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 437 .TextFileStartRow = 2 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = True .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With ' ws.Name = "testing" ' updates the date Range("C12").ClearContents Range("C12") = Format(Date, "mm-dd-yyyy") ' This will focus this worksheet after upate Sheets("NA_Accounts_List").Visible = xlSheetVisible Sheets("NA_Accounts_List").Select MsgBox "Imported data successfully!" end sub </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload