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
GPSPOWAsked:
Who is Participating?
 
GPSPOWAuthor Commented:
Martin,

The sheet is tied to a pivot table.  When I delete any of the rows or columns it messes up the cell references to the pivot table.

I found an alternate utility to accomplish this task.  I created a MS-Query using the csv file as a source.  It works perfectly.

Thanks

Glen
0
 
Martin LissOlder than dirtCommented:
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
0
 
GPSPOWAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.