asked on

Importing CSV data into Excel using VBA

I have built a macro that imports data from a csv file to a specific worksheet tab. The macro works well with only one small problem. The old data needs to go away and replaced by the new data in the CSV file. Currently when I refresh the import, the old data is shifted to the right and the new data goes in to the old columns on the left.

Is there a flag in the Import connection properties that will let you overwrite the old data and have the data areas shrink or expand as needed? Or do I just need to do a simple copy and paste of the data?

Thanks

Glen

Martin Liss

How about clearing the sheet before the rest of the macro runs? This will totally clear the sheet

ActiveSheet.UsedRange.Clear

but if you have headings you want to preserve then do this where in this case the "1" says the heading has only one row.

ActiveSheet.UsedRange.Cells.Offset(1, 0).ClearContents

ASKER CERTIFIED SOLUTION

GPSPOW

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GPSPOW

ASKER

Excel allows you to use many file formats as a source for a MS-Query rather than importing the whole file to a worksheet tab.

By using the MS-Query you have more control over the data to use as many of the columns of data in the source file as you need. It also allows you to perform calculations within the the query before it gets posted to your destination tab.

If you are familiar with SQL programming this can be very useful.

One more advantage is the refresh feature of the query. The table within the destination tab rewrites itself every time you refresh.

Glen