{"id":5983,"date":"2018-03-18T09:30:14","date_gmt":"2018-03-18T09:30:14","guid":{"rendered":"http:\/\/putridparrot.com\/blog\/?p=5983"},"modified":"2018-03-18T09:30:14","modified_gmt":"2018-03-18T09:30:14","slug":"loading-an-csv-file-into-a-workseet-in-excel","status":"publish","type":"post","link":"https:\/\/putridparrot.com\/blog\/loading-an-csv-file-into-a-workseet-in-excel\/","title":{"rendered":"Loading an CSV file into a workseet in Excel"},"content":{"rendered":"

Let’s assume we have an Excel worksheet with some nicely formatted headers and just above the row containing the headers we’ve placed a button which will we use to load a csv file into the rows below header row (without losing our formatting etc.)<\/p>\n

Go to the Developer tab in Excel and select Visual Basic then create a subroutine like this<\/p>\n

\r\nSub LoadFile()\r\nDim fileName As String\r\n   \r\n   fileName = "c:\\myfile.csv"\r\n\r\n   Set ws = ActiveWorkbook.Sheets("Import")\r\n\r\n   ws.Rows("3:" & Rows.Count).ClearContents\r\n    \r\n   With ws.QueryTables.Add(Connection:="TEXT;" & fileName, Destination:=ws.Range("A3"))\r\n        .TextFileParseType = xlDelimited\r\n        .TextFileCommaDelimiter = True\r\n        .AdjustColumnWidth = False\r\n        .Refresh\r\n    End With\r\n    \r\nEnd Sub\r\n<\/pre>\n
I’ve simplified the code to have an explicit filename, instead of getting the filename via a folder selection dialog box or from a cell on the spreadsheet.<\/em><\/p>\n
Simply get the active worksheet (mine’s named Import) and from this we’ll clear all row’s below our heading row (my rows start as row 3). <\/p>\n
Next we use the QueryTables object and add the “Connection” supplying the filename that is to be imported. The destination is the start column\/row where we want to place our data.<\/p>\n
AdjustColumnWidth = False is useful to stop the import resizing your columns, the Refresh procedure will cause the spreadsheet to redraw itself.<\/p>\n
Now this will load the CSV into the area below the column headings and not resize the columns to match the data size (hence not ruin existing formatting).<\/p>\n","protected":false},"excerpt":{"rendered":"
Let’s assume we have an Excel worksheet with some nicely formatted headers and just above the row containing the headers we’ve placed a button which will we use to load a csv file into the rows below header row (without losing our formatting etc.) Go to the Developer tab in Excel and select Visual Basic […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[54,207],"tags":[],"class_list":["post-5983","post","type-post","status-publish","format-standard","hentry","category-excel","category-vba"],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/posts\/5983","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/comments?post=5983"}],"version-history":[{"count":2,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/posts\/5983\/revisions"}],"predecessor-version":[{"id":6004,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/posts\/5983\/revisions\/6004"}],"wp:attachment":[{"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/media?parent=5983"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/categories?post=5983"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/tags?post=5983"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}