Wait for Text Import to Finish before Sorting

**tomlancaster** · 10-27-2009, 10:38 AM

Hi,

I have a workbook which Imports Text from a File when it is First Opened.

Excel prompts me initally to either Enable or Disable automatic refresh and then imports this text data into the Workbook.

What I would like to do is as soon as it has finished imported the text into the Workbook, to then Sort the data in collumn B in Ascending order.

I thought I could do this using ThisWorkbook but the actual prompt to either enable or disable the refresh doesn't appear until the code runs.

I am not an expert on VBA so not sure how I would go about setting this up so any assistance would be greatly valued!

**Richard Schollar** · 10-27-2009, 12:50 PM

Hi Tom

Is all the data you want to sort that which you have imported in the first place? If so, it should be possible to spec that the data is sorted before performing the import (you can do this by modifying the SQL being used in the import). How did you create the import routine? Was it via Data>Import External Data?

**Richard Schollar** · 10-27-2009, 01:16 PM

Tom

By far and away the best method would be to direct the SQL inherent within the query to pre-sort the data before it is imported, however, you can set up an event to monitor the query and respond once it has been performed:

1. In your workbook, open up the VBE and double-clcik on the ThisWorkbook icon. Place the following lines of code in the ThisWorkbook module that will open:

Please Login or Register  to view this content.

It will then sort the result data of the first querytable on the first sheet in the workbook after it has been updated, based on the second column in the data.

You will need to amend the "Sheets(1)" bit to ensure the correct query table-containing sheet is specified.

Richard

**tomlancaster** · 10-27-2009, 02:34 PM

Thanks Richard for your response. I haven't tried it on the workbook I have created yet but will try in the morning but I do have a feeling this will not work.

My Workbook at the moment has a Workbook_Open() routine which activates a sheet - It does this before even the prompt to enable automatic refresh. I was assuming that on the sheet it activates I could have a Worksheet_Activate() which could have a macro to sort the data after it was imported but this hasn't worked.

The data was imported using "Data>Import External Data" and the properties are set to import on file open.

Any other suggestions so I am armoured for work in the morning?

**Richard Schollar** · 10-27-2009, 03:07 PM

The code I supplied will only activate after the query table has been updated - you can add the line within the Workbook_Open routine to your existing Workbook_Open routine. There should be no reaqson why it wouldn't work.

When you imported the data, did you spoecify New Database Query or did you choose the Import Data option?

**tomlancaster** · 10-27-2009, 03:22 PM

Cheers Richard,

I will try in the morning. I imported the text file via a specified path. This file is a csv updated by another application. Will this be an issue?

**Richard Schollar** · 10-27-2009, 03:43 PM

No it shouldn't be.

**tomlancaster** · 10-28-2009, 08:56 AM

Hi, I just wanted to say thanks! Your code worked perfectly!

Thanks again

Wait for Text Import to Finish before Sorting

LinkBack

Thread Tools

Rate This Thread

Display

Wait for Text Import to Finish before Sorting

Re: Wait for Text Import to Finish before Sorting

Re: Wait for Text Import to Finish before Sorting

Re: Wait for Text Import to Finish before Sorting

Re: Wait for Text Import to Finish before Sorting

Re: Wait for Text Import to Finish before Sorting

Re: Wait for Text Import to Finish before Sorting

Re: Wait for Text Import to Finish before Sorting

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions