Import Macro Problem

[santeria]

When running the IMPORT macro to update raw data, the macro comes up with the error Runtime Error 1004.

The Debug error Displays in yellow over the line I have the Red X dot/ball against.

Anyone see anything wrong in the macro , please ???

Ta




Sub IMPORT()
'
' IMPORT Macro
' Macro recorded 10/6/2003 by cmilton
'

'
Sheets("RAW SKILL 77").Select
ActiveCell.Select
With Selection.QueryTable
.Connection = "TEXT;C:\EXPORTS\10_OCTOBER\CMS\DAILY\1.txt"
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1)
.Refresh BackgroundQuery:=False
End With
Sheets("RAW SKILL 17").Select
With Selection.QueryTable
.Connection = "TEXT;C:\EXPORTS\10_OCTOBER\CMS\DAILY\2.txt"
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1)
.Refresh BackgroundQuery:=False
End With
Sheets("SKILL 77 SERV LEVEL").Select
With Selection.QueryTable
.Connection = "TEXT;C:\EXPORTS\10_OCTOBER\CMS\DAILY\3.txt"
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.Refresh BackgroundQuery:=False
End With
Sheets("SKILL 17 SERV LEVEL").Select
With Selection.QueryTable
.Connection = "TEXT;C:\EXPORTS\10_OCTOBER\CMS\DAILY\4.txt"
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.Refresh BackgroundQuery:=False
End With
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("SUMMARY").Select
End Sub

 

[santeria]

Soundex works great.
I guess the trick is, how do you get the function to work as something to insert into a file.
The Query box is great, but the output going into a cell would be brilliant.

Ta

 

[MWDeVries]

I ran into a similar problem when having a macro switch sheets and do some manipulations. The problem I encountered was that the button on the toolbar did not give focus to the sheet I switched to and therefore the code failed. I think I found the solution in MS Knowledge base.

Try adding the following line in your code just before the line where the erro occurs.
ActiveSheet.Cells(1, 1).Select

This makes sure the ActiveSheet has the focus.

 

[santeria]

Thanks, I'll try your solution on the original Macro. Once I get time to implement it when the current project ends. Or if I get bored after lunch... which ever comes first :wink:

I successfully implemented Dave M's new import macro, which does the same job with less code... so I learned something.

But it's always good to get a new perspective on something that I thought was okay ( I had no idea why the thing suddenly stopped working).

Anyway, MS Knowledge base is something I am definitely planning on getting, but my next buy is the Knowledge CD from Mr Excel.

So far, this is definitely the place to go for powerful solutions.


Ta

 

[MWDeVries]

The Microsoft Knowledge base is a searchable database of issues and articles, etc. It is free and it is online. It has a lot of information that you can search through.

This is a good site too. There are many experts who have run into the same roadbloacks and have found the workarounds, etc. or may point you in a different and better direction.

The knowledge base article I am referring to is at

http://support.microsoft.com/default.aspx?scid=kb;en-us;169103&Product=xlw97

 

[santeria]

Thanks.
I had thought that the full MS Database was on Subscription CD, at least the pretty nifty stuff anyways
The CDs I used to get had a Subscription Basis, and even had a Developers version.
Anyway, if it's all online, so much the better.

And yes, this site is excellent.
My aim is to pick up material and learn so I can apply what I learn as best i can. And there are so many people here who are just fabulous. In a Short space of time, the things I can do has grown fabulously, and all because I have looked at things here and applied myself as best I can.

Sheer gold !!! Absolutely wonderful site, and brilliant comunity in all respects. I feel proud and lucky to be a part of the group, albeit a very novice user of the products in the current incarnation of my work role, and the help here is magnificent.

Much Appreciated.

Ta

 

[MWDeVries]

What you may have been thinking about was the MSDN Disks, (MicroSoft Developers Network). I used to get those disks. It's quite similar, but more pointed to programmer/developers. The Knowledge base is usually pretty good if you are looking for a reason you are getting some error that you don't think you should be getting.