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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.