Problem using Data Import and VBA

[JazzSP8]

Hi All

I am working on a workbook that will automatically import data from system reports (tab delimited), add up a column, dump the figure in a predefined cell and move onto the next report.

I got it working with one set of data and thought I was laughing, how wrong I was, I tried it with a new set of data and my cells were all given a value of Zero.

On investigation I discovered that if I recorded a macro to import the new data there was a subtle difference.

Originally when I recorded the file I got this;

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\" & Environ("Username") & "\Desktop\Report\ACE", _
Destination:=Sheets("Import").Range("A1"))
.Name = "ACE"

But I tried again this morning and got this;

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\Desktop\Report\ACE", Destination:= _
Sheets("Import").Range("A1"))
.Name = "ACE_54"

The ".Name" has changed.

I can guess this is because Excel has stored this number somewhere as either a count or a tempoary file?

Is there something I can change to make it work every time I want to run the macro?

As always, thanks in advance for any help that can be given...

 

[Richard Schollar]

Hi

That doesn't appear to be the only thing that has changed - You have it pointing at a directory with name Environ("Username") in the original?

Anyway, check Insert>Names>Define - this is where the query settings are held (ie in a workbook name). One gets generated each time you use the Add method of the QueryTable object. I don't *think* that the zero values is down to this - are you sure the source data is in the same structure?

 

[JazzSP8]

Hi Richard

Sorry, yes - I added the Environ("Username") part of the path so then it could work on other peoples computers.

I checked out the Insert > Names > Define and you were right, the names are all listed in there, I am just going to delete them all now (there are 20 queries with 53 of each - D'oh!) to see if that solves the problem.

The data is in the same format, that was one of the first things I checked.

I shall post back and let you know how I get on

 

[JazzSP8]

Hi again Richard

Just to let you know that I deleted all the old queries and am back up and running again now.

Thanks for your help .. Again ..