• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 639
  • Last Modified:

Load CSV data into an excel spreadsheet

Hi I have an existing excel spreadsheet with some data in, what I want to do is (using VBA) create a function that takes an external CSV file (located in the same directory as the excel spreadsheet) and load the data within it into the spreadsheet. The CSV file contains three columns of data to load I have already written the code to make space in the spreadsheet i.e. Columns have been shifted three columns to the left... And ideas?
0
Blowfelt82
Asked:
Blowfelt82
  • 5
  • 4
1 Solution
 
SteveCommented:
This will create a QueryTable link to the file, once you have the table in Excel, right click it to update from the cvs file each time.

Sub CSV_Import() 
Dim ws As Worksheet, strFile As String 
 
Set ws = ActiveWorkbook.Sheets("SheetName") 'set to current worksheet name 
 
strFile = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Please selec text file...") 
 
With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1")) 
     .TextFileParseType = xlDelimited 
     .TextFileCommaDelimiter = True 
     .Refresh 
End With 
End Sub

Open in new window

0
 
Blowfelt82Author Commented:
That looks good, I was hoping to make a fully automated solution with no user interaction if possible though. The filenames involved etc wont be changing?
0
 
SteveCommented:
Once the CSV file has been added via query... there is no more need for the VBA... and the table can be set to refresh on file open if you don't trust the users to be able to right click and refresh. (right click, data range properties, refrsh on open)
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
SteveCommented:
You could also change the strFile = to the filename of the .csv file and the dialog will not pop up on macro refresh.
0
 
Blowfelt82Author Commented:
Ok sounds better, just to double check though, the data is just being uploaded temporarily as part of other processing and will be deleted after - also the csv file will be deleted later... The query table wont be dependant after this will it...
0
 
SteveCommented:
If you delete the table/columns the query will go too.
The table will be hooked to the csv, so if you refresh after deleting the csv, it will fail. But providing you do not refresh after deleting the file all will be cool.
0
 
Blowfelt82Author Commented:
Ok, one thing I forgot to mention (the points are alreadt yours) I would like to filter the data that is imported based on the first column of CSV data. This is just a filename field so would like to filter by file extension - is this possible?
0
 
SteveCommented:
It is, just need to add a filter to the table and then filter on the column.
Record yourself doing the actions in Excel, and this will get you a starting bit of code.

If you need more help from there feel free to ask a fresh question and we will be happy to answer it.
0
 
Blowfelt82Author Commented:
Thanks for your help.
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now