JazzSP8
Well-known Member
- Joined
- Sep 30, 2005
- Messages
- 1,227
- Office Version
- 2013
- 2010
- Platform
- Windows
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...
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...