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]

All 4 files are there, and in the same directory.

Thats what I can't figure.
Is there a line of code that is wrong ??


Ta



************************************

Hey wait a minute, doesn't runtime 1004 mean the file is missing? Check to be sure there is a file in the folder....

Good luck.

Dave M.

 

[Davers]

No I understand...especially at the end of the day, you shouldn't make any big changes....above I did mention I've gotten the error 1004 when a text file was missing.... other than that, it looks like it should work...

Sorry I can't be more help...

Dave M.

 

[santeria]

no probs.
I guess that since the data entry is working, well I should be happy with that.
It's just not 100%, since the error.

I suppose the test will be the Summary move macro... which I better test now

Ta




**************************

 

[santeria]

Okay,
Thanks for this... I have a few hours ( with bugger all to do at this juncture).
So, I may as well look to repair the Import file.
I am not real clear on the separate subs for the import.
and I think that the Reference point for my original macro was A1, and it imported to the sheet, but I better double check after I run a temp file to check the placement.

Anyway, Much appreciated.

Ta.



**********************************

You run the "Sub doimPrtall() " part and it calls the "Sub doimPrt" part when it needs it....

Good luck, and hope this helps,

Dave M.
Hope this helps a bit....[/quote]

 

[Davers]

Hi again, did you say that your code did import the files, but something else was going wrong? Or it only imported some of the files....or none of the files...

Dave M.

 

[santeria]

The original Macro I had failed at one point only, and yet it still inserted the main data, however the date stamp that proved that the data was from a particular time, was never imprinted.
And as I pointed out above the error was on one line only, and yet the line seems fine.

I ran your macro, after adjusting the A2 reference to A1, and all worked fine, with no errors.

Incidentally, how do you write comment in a macro that does not run ?
Especially something like, "Tested at 1pm Thursday 23" and stuff like that.

Beautiful macro. But the other one, created with the help of others here, worked just as well up until a few days ago.


Ta



*************************

Hi again, did you say that your code did import the files, but something else was going wrong? Or it only imported some of the files....or none of the files...

Dave M.

 

[Davers]

I'm a little worried that part of the code stopped working, especially since it looks exactly the same as the parts above...however, the code you posted is only importing data, it's not doing anything else. I don't see anything that would put a date stamp on your sheet unless I'm just not seeing it. I am by no means an Excel "guru"... Could you give me a step by step of what the code should be doing? If it wouldn't take too much time? Are you getting your data from Lucents CentreVu? (That would be cool!) or do you guys have a different program you use. If you are using CentreVu, then I know what your .txt files look like and might be able to help with your dates etc...I am fortunate enough to be the Reporting Specialist here...meaning all of my time is spent creating reports, automating reports, thinking of new ways to do reports.... etc......

Especially something like, "Tested at 1pm Thursday 23" and stuff like that.

if you mean how to date stamp a specific cell on a sheet..then that is possible using the date from your text file if you'd like or using Excels Now() function.

Hope I can help!!

Dave M

 

[Davers]

Doh, and sorry, if you mean how do you put comments in your code? Just put this little symbol ' In front of what you want to be a comment. It's also a handy way to skip a line of code if you don't want it to run for some reason.

Dave M.

 

[santeria]

Thanks about the code comments stuff.
Thats just for me, so I can remember when I tested the thing, and so if anyone else has to use the code, they can know what I have done.



Okay. to the other thing:
My Macro that was working before used Four txt files drawn from CMS. I do not have command line access to CMS, and I do not know if the core is SQL or something useful like that. So I manually run reports and export the files to txt files that are very excel friendly.
The Macro was placing the files in my Raw data section of the first file ( usually date for the day of report). Then I have links from the page summaries to four sections that are just PAST LINKED.
Two Summaries are basically my info, then the top summaries go to produce reports that the Bosses want.

Thats basically what the import files macro does.
And I still cannot see why the macro fails... it's like something in the program has just suddenly stepped in... totally odd.

As far as I know , a module cannot get corrupted, if that was the case, I'd just redo the Macro.

However, at this point, your macro is sleeker than mine, and I installed a icon on my Temp Macro Toolbar, and the Macro imports quicker than the multiple steps I did before.

Ta

 

[Davers]

One other thing popped into my head, if your date stamp is actually a calculation, maybe your sheet somehow switched to manual calculations. Meaning it will only calculate when you press...I think it's F9. To check that setting, go to your Tools - Options menu, then select the Calculation tab, and make sure Calculation is set to Automatic. I've had a spreadsheet or 2 change on me in the past for no explicable reason. Other than that, like I said, your code is almost exactly the same as mine, only in mine, I'm telling it to repeat itself....it should work. Really odd.

Good luck and feel free to e-mail we with any reporting questions or comments, or anything neat you may have figured out. As I said, I work in the Work Force Planning department of a call center, and mainly do all the reporting for them. We haven't had to dabble into scheduling yet..and I hope we never will....

Have a good weekend,

Dave M