Import CSV into existing table - overwrite contents from old CSV

[-mmf-]

Hi all,

I am currently struggling with the following issue and I can't seem to find anything fitting.

I have a spreadsheet, one table (named table 'LogTable') which I prefilled from a CSV. The table itself should contain the CSV contents (separated) in the first columns, the other columns are calculated.

Excel automatically creates four connections:
- Connection
- log1 (which points to the CSV, but says "Not used in this workbook"
- ThisWorkbookDataModel
- WorksheetConnection_Test.xlsx!LogTable

I would like to be able to change the CSV (which always has the same format), so the contents of the table get automatically updated.

Once I made it work, but I can't remember how. The workaround is bad, as it breaks my references in the caluclated cells:
- Convert to range
- Import CSV via Get External Data (which is greyed out if it is not a range)

Is this even something I can do?

Thanks for any hints,

MMF

[dflak]

There are three ways I can think of doing this.

Use VBA to clear the table and then copy / paste the contents of the CSV file into the now-blank table. This has the advantage of being flexible such as when the CSV file has some sort of a constant "root" name and a predictable date stamp.

If you have MS-Access, then create a link to the CSV file. Write a query in MS-Access and then connect to the query as an external data source. This works particularly well when the new file is named the same every day. Otherwise some VB would be required to change the connection.

Use MS-Query: First do a one-time creation of a directory as a text data source. I can provide documentation with screen captures for this. Then use MS-Query to connect to this data source. I can give you instructions on that too. Then it is simply a matter of selecting which table you want (any text file in the directory) and drag and drop the fields in a fashion similar to using an MS-Access query interface. You can then even have the connection refresh automatically when the file opens. This method has the same limitation as MS-Access if the file name changes daily.

Which sounds like it would work for you? Method 1 is rather simple to implement if you don't mind having a macro-enabled workbook.

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.