Import Macro Problem

santeria · Oct 22, 2003

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

Davers · Oct 22, 2003

Hi santeria, am I to understand you work in a call center??

Dave M.

santeria · Oct 22, 2003

I write reports for areas including a Call centre, Web site, and an IVR.

Plus reports based on SAP info.

Other than that, I have had to do Scheduling of staff so far.

Anyway, I don't take calls

.... The data I have to work with is mostly confidential.

Except the End of Day report, which is just a pain considering that if I had Crystal reports I could do the whole thing in two clicks.

Me on the phones here would be strange... I can't understand most of the callers, and way too many people just comment about my accent

Anyway, it pays the bills, and ALL calls are incoming, except calls out to people who want info called back.
It's not a telemarketer place !!!

Ta.

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

Davers said:
Hi santeria, am I to understand you work in a call center??

Dave M.

Davers · Oct 22, 2003

Small world, I work in a call center as Work Force Planning, are you running a report to find your average talk times, handle times, etc?

Dave M.

santeria · Oct 22, 2003

Yup, it's the end of Day ( EOD ) report, done at just after 5:30 pm.
So, I have tried to have everything automated.
then redone in the morning based on Full day data ( people call outside the official open hours for reasons unknown to me... not that I care... so long as the figures work, and the managers, Supervisors and etc keep giving me a cheque each fortnight )

Just as I am getting closer to automating the email, well, now my text import of the raw CMS data ( for the calls, Average times, Handle times etc) is giving the above error.

So if you can give a hand...

Ta

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

Davers said:
Small world, I work in a call center as Work Force Planning, are you running a report to find your average talk times, handle times, etc?

Dave M.

Davers · Oct 22, 2003

I'm trying to convert my reports code to work with your sheet names and text files...I do mine slightly different though, I import a text file to a "RawData" sheet, perform any calculations that need to be performed, and then the numbers get copied and pasted to the correct centers sheet onto the correct date....then it imports the 2nd text file...etc....I'll see what I can come up with...although I only have another 15 minutes of work

If not, I'll still be able to see what I can come up with tomorrow...

Arent' automated reports nice!!!!

Have a good night...

Dave M..

santeria · Oct 22, 2003

I just have four files to import, two are strictly speaking call stats, the rest are service level reports.
I just extract the info. The Macro that is having a fit places the files onto four raw data sheets, this then is summarised onto two sheets, and the exact ones for each report asked for is transferred to the EOD sheet.

The sheet was working okay up until yesterday afternoon.

Odd thing is that it place the data in okay, but fails on time import for the date stamp.... it is just a pain to have something that was working suddenly go ka-blewey

Ta.

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

Davers said:
I'm trying to convert my reports code to work with your sheet names and text files...I do mine slightly different though, I import a text file to a "RawData" sheet, perform any calculations that need to be performed, and then the numbers get copied and pasted to the correct centers sheet onto the correct date....then it imports the 2nd text file...etc....I'll see what I can come up with...although I only have another 15 minutes of work If not, I'll still be able to see what I can come up with tomorrow...

Arent' automated reports nice!!!!

Have a good night...

Dave M..

Davers · Oct 22, 2003

Odd...I'm not sure what's going on, it looks like it should work...I do have a suggestion for your code though, you can eliminate all the parts that are the same in your code. Below is an example that will import your text files and paste them into the sheets the are defined in an array....I've tested it with my .txt files and it worked for me....hopefully I didn't mess anything up when I switched the file names and directory names to what you have in your code....

Code:

Sub doimPrt(ByVal fName As String, ByVal shtNme As String, ByVal destName As String)
    Const myDir As String = "C:\EXPORTS\10_OCTOBER\CMS\DAILY\"
    
    With Sheets(shtNme)
    
        With .QueryTables.Add(Connection:="TEXT;" & myDir & fName, Destination:=.Range(destName))
            .Name = TableName
            .FieldNames = True
            .PreserveFormatting = True
            .TextFileParseType = xlDelimited 'not sure if you need this line
            .TextFileTextQualifier = xlTextQualifierDoubleQuote 'not sure if you need this line
            .RefreshStyle = xlOverwriteCells
            .AdjustColumnWidth = False
            .TextFileTabDelimiter = True
            .TextFileColumnDataTypes = Array(1, 1)
            .Refresh BackgroundQuery:=False
        End With
    
    End With
    
End Sub

Sub doimPrtall()
     Dim fNames As Variant, shtNmes As Variant, DestNames As Variant, i As Long
    
    fNames = Array("1.txt", "2.txt", "3.txt", "4.txt")
    shtNmes = Array("RAW SKILL 77", "RAW SKILL 17", "SKILL 77 SERV LEVEL", "SKILL 17 SERV LEVEL")
    DestNames = "A2"
            
    For i = LBound(fNames) To UBound(fNames)
        doimPrt fNames(i), shtNmes(i), DestNames
    Next
    
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst 
Sheets("SUMMARY").Select 

End Sub

What this is doing is basically looping through your text files and sheets, importing in the order above, so 1.txt will go to RAW SKILL 77 and import to cell A2, 2.txt will go to RAW SKILL 17 and import to cell A2, etc...if you need them to paste to a different cell, just change the DestNames = "A2" to whatever cell you'd like. Or if it's a different cell on each sheet, make it an array like shtNmes, i.e. DestNames = Array("A2", "C4", "B6", "G12") and change you DestNames in between the For...Next from just DestNames to DestNames(i)....

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....

Davers · Oct 22, 2003

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.

santeria · Oct 22, 2003

Thanks.
It looks like a great fix on my sheet, but my question is... can you see why my original macro above should fail as I described?

Thats what I need to know for this afternoon.
If I adopt the change you suggested, I'll be doing about 2 hours change in some other elements... not that the change is bad, just that the files I have are based on the macro that has the error.

So.... is the original macro somehow faulty ???

Ta

Import Macro Problem

santeria

Well-known Member

Excel Facts

Davers

Well-known Member

santeria

Well-known Member

Davers

Well-known Member

santeria

Well-known Member

Davers

Well-known Member

santeria

Well-known Member

Davers

Well-known Member

Davers

Well-known Member

santeria

Well-known Member

Similar threads

Forum statistics

Share this page

Import Macro Problem

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock