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]

Thanks....

I'm still have headache style problems with my few macros on my EOD report.
The one that compiles the list of txt files is making a call to a file on the 28th october ( XLS file).

If I check out the VBA edit area, with only one file open.
I have, Three Instances of the 28th oct, one instance of the 29th October, a CMS1 , a CMS.XLT, an XLA, all of those VBA project listings, as well as the current workbook.

Is this meant to display all of this? Or is this where I am getting the problem where I have the 28th opening each time I renew the data for a period ( hourly or half hourly, depending on what I feel like doing at the moment).

Totally Confused over this one, since the macros run, and they have no mention of the file of the 28th october.


Ta.

 

[santeria]

I just checked the problems with the macro again, and it's all the macros.
I have it so that they are run on a one click item on a toolbar.
If I go TOOLS --> MACRO -- > MACROS, and then select the relevant Macro it runs perfect.
If the icon is used , the Macro picks up on the October 28th file, and etc.
This has happened rather suddenly.

Is it just a matter of resetting up the Icons for the MACROS ??

Ta

 

[santeria]

Okay... Answered my Own question.
If I set up a Macro button to operate in the toolbar of Excel, it references the current day report... so a report icon macro set up on the file for 28th will refer back to that file.

Is it possible to set up an icon which is referring just to the current file, or is a button on the sheet needed? So that the icon created on one day will always refer to the macro in the file, but not to a Macro on a specific date file.

Or does a Macro have to be done which will update the icon references in the toolbar....

OR... what is the convention for running macros but one click instead of 5 or so clicks.


Ta

 

[santeria]

Not to worry....
I discovered the wonders of buttons on the file sheet....
Will wonders never cease
I also found some other solutions to form submission, and subsequent creation of a database, but thats another story, AND another add-in

Ta.

 

[Davers]

Hey, how's it going? Sorry I missed all of your posts....but at least you were able to answer your own questions! If you like, feel free to e-mail me at work with any cool suggestions or questions you may have...although I must say again, I am by no means an expert at ANYTHING I do....

Dave M.

 

[santeria]

going pretty well actually.
I hope things are well for you.

Actually, the big question I have relates to the start of a Macro that will probably end being behind an array of formula ( I hope ).

First thing I need to sort out is a link to other files that have a list of names. The idea being to key in either the name, or part of the name, and have the formula or link, return the name, or act something like the autofill function at the end of a list....
Example.... if the link works the way I want it to.... then in finding the name "Sumners,David" I would start typing S then u, and if unique, the rest of the name should populate. I am not sure of this is possible, but it's the start to a form field that will eventual trigger a populate of an assessment report.

Where it gets interesting for me, is the form will hopefully populate a database that I can then play with , and massage into some useful reports.

But, first things first.... I need to figure how I can link a file with one field requiring a name to be entered, to a file with a list of names. I thought I could do it with a particular type of connection that requires a table, and I have a list of Names in column A, 100 names, and I can't seem to get things happening.


Ta.

 

[Davers]

I'm not sure where I downloaded it from...it's been awhile, but I have a workbook that shows how a soundex function works in Excel. It basically uses a custom function (SOUNDEX) that creates a 4-digit code that allows names to be grouped or searched by sound rather than the exact spelling. This function was developed by Richard J. Yanco. It's pretty cool. If you like, I'll e-mail it to you, I don't know how to post a workbook on this site...

I've actually dabbled in Access to create my employee database. Querying seems to be more powerful, and you can export everything to Excel if you want....

Let me know if you'd like that workbook...I wish I could remember whose web site I got that off of....

Dave M.

 

[santeria]

Yes, I'd love that file emailed to me.
I'm at work, so I need that here... do you have my work email??

Access is probably the best way technically, however, since most of the supervisors and managers supply info in Excel, I have to keep it to that at this stage of the game.


Anyway, I presume I can email you from the link, I guess.

Ta

 

[Davers]

Hi again, my e-mail for work is in my profile...I checked for yours... I didn't see it in your profile....

Talk to you later,

Dave M.

 

[Ivan F Moala]

Have a look @ Users Post for the email buton

eg

<body><tr valign="bottom" align="center">
<td nowrap> 

</td></body>