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]

I checked all this... which is why it is so odd that the file import should suddenly halt at one point.
The scheduling thing i have to do is so ho-hum... I have to change the leader dates for any given week. And occasionally delete people who have left for saner jobs, as well as change Schedule files with block time allocation, as well as a file that has specific time statements... such as 8:15 to 5:15, and states the time of break, and states the time for lunch.
The block allocation file shows the time allocated, as well as calulating load of operators per time segment.
The upshot of that is that if a time segment is having too many abandoned calls, then the time sheduling has to be changed.... and I thought that this part of the job was the role of the Operations managers??!!

Ah well.... at least I still get paid for the job.
But, plans for change in the pipe-line should change things.

Ta

 

[Davers]

and I thought that this part of the job was the role of the Operations managers??!!

Heh, I know exactly how you feel!

Dave M.

 

[santeria]

Thanks for all the help.

The thing that intrigues me about the Macro you created is ( apart from the fact that it works superbly), how does the string Values work, in a syntax sense; and also the Array definitions... do these work sequentially through ?

Also, the string referring to directory...
I have a Create File, or actually open file Macro that has a date value that takes it's value off the current day, viz...
****
"C:\REPORTS\DAILY SKILL 77\" & Format(Date, "mm_mmmm") & "\" & _
Format(Date, "mmm dd\.\x\l\s"), FileFormat:=xlNormal, _
****
Which seems to define the directory date in a relative and not absolute sense.
Would it be possible to have a form of the Import macro as the Open File date definition line as above, or would the line have be edited manually as change happens?

as always, the plan is to try and Automate things.
However the reports happening currently, are once yearly... so I guess if I survive this open enrollment, and the SAP excel sheets ( if they give them my way, and don't have the data generated manually like a few weeks ago), I can probably develop a set up which works better next year.

That's if the the outsourcing contract continues

I don't know how I'd do if I were transferred down to Jacksonville or up to Toledo

Ta

 

[Davers]

Heh...well, I'm sure I'm not the best person to explain exactly what is going on....I am still a fledgling when it comes to VBA...but I'll give it a shot. The Array question I can answer...yes it is sequential. It takes the first value in fNames, then the first value in shtNames, then the first value in DestNames, or in this case, the only value in DestNames, and it repeats the process moving on the next value until it runs out of values. I'm afraid I'll have to defer to explaining the syntax for the string values...I don't know it well enough to explain. Sorry...Jimboy, if you're out there...bail me out here... :wink:

your line above:

"C:\REPORTS\DAILY SKILL 77\" & Format(Date, "mm_mmmm") & "\" & _
Format(Date, "mmm dd\.\x\l\s"), FileFormat:=xlNormal, _

would work. Is that where you get your data, or where you want to save your data...?

A warning about automating everything...I currently have roughly...15 or so reports that are fully automated. They querie our CentreVu application to get the current, or previous days data, depending on what I need, they then import the data, calculate the Average Handle Time...etc. and paste the data to the correct departments sheet next to the correct date. The problem is...I have to really think to remember how to get all the data I need manually. :wink: The reports have been automated for about 6 months or so, and I'm slowly forgetting how I used to do them. Heh...oh well....

Anyway, good luck with your contract! If you need help with fitting your directory into the code, let me know...I'll help as much as I can...

Have a good day,

Dave M.

 

[santeria]

Thanks.
I'm a fledgling to creating VBA from scratch.
If I can understand the syntax, then I'm fine. Case in point, I have the general format for an email to Lotus Notes macro, but I don't know what to insert in the macro at key places.
When I used to do mr fix-it PC support work, I just used to look at the problem macro, compare it with a working macro in a different section of the company, and then make alterations. Not exactly rocket science, but it saved the general users from doing the same thing.

The really involved process, comparatively, was which ODBC driver to use, since for most instances, the developers and programmers did not know 100% of the time... they guessed too often, and what I had to do was find a working version out of about 1,000 users, and transplant the setup.

Anyway, VBA work is intriguing, but mostly it's a means to an ends, and I am still on a learning curve in this more raw side of the tracks.

Okay, I think I understand the array setup.
So long as I can mimic a few instances, then I'll be fine eventually.

So long as I have instances of "string theory", and so long as I don't have to do maths on a par with Wave particle theory, I should be fine


The automated section is where the Month, and the day is automated.
Someone here set that up for me. and thus far looks like a good
little trick to know. It's where I save the data to.
Since the file it comes from is about opening a new file for the EOD.

The genius's I report to have not given me access rights to data sources so far, so I can't access data directly, which is a pain, since I am used to that from previous work as a PC tech.

Since I document everything I do, automating everything is not a problem.
I either do full on step by step documentation, or I annotate the code I do in various systems.
My main programing experience has been Ericsson systems, SCO Open Server SQL, and HTML of the vanilla variety. Usually I can print out steps in the automated procedures, and have them on file, so getting back to square one, after either a systems crash, or some genius administrator changes root directories, allows me to redo my original steps fairly easily.

The contract is outside of my sphere.
My job has "permanent status", although that does not mean much ultimately.About all I have to worry about in the current situation is where they might transfer me if the contract goes south.

You have a great day too.

Thanks for all your help.
I appreciate your time and assistance greatly.


Ta

 

[Davers]

Since you were mentioning directories you are saving to, I thought you might find this workbook interesting. I finally figured out a way (which is why it isn't very elegant), to automatically save to a directory that changes monthly. The example sheet below gives the information the code needs to select the correct directory based on the date the user inputs before they run the code. Here is what the cover, or setup sheet looks like with formulae:

AutoVLReport1
	B	C	D	E
1
2	EnterstartDate:
3	DateAutoEnter	1/5/1900
4	Center:	WebSupportGroup
5	CategoryCode:	12022
6	Position	WSG
7	ActivityType	="Call-Inbound"OR="ChangeOwnership"
8	TodaysDate	28-Oct-03
9
10		0100-0105
11
12		VLReports2003\VLReports1003\
Sheet1

and here is what the code looks like:

Sub LaunchSiebel()

    Dim strtDate, endDate, Cntr, catCode, Pos, crntDate, acType, combDate As String
    Dim myDir As String
    
    Dim lngFileSize As Long
    
    
    strtDate = Range("strtDate")
    endDate = Range("endDate")
    Cntr = Range("Cntr")
    Pos = Range("Pos") ' "Position" field
    catCode = Range("catCode") '"Category Code" field
    crntDate = Range("crntDate") ' enters date report ran
    crntDate = Format(Now(), "mmdd")
    acType = Range("acType")
    combDate = Range("combDate")
    myDir = Range("myDir")
    

    ' Code courtesy of "www.TheScarms.com"
    
    ProcID_Siebel = Shell("C:\Progra~1\siebel\BIN\siebel.exe /c c:\Progra~1\siebel\bin\uagent.cfg /d Server /u dmorri18 /p fidget")
    'goes to the "All Activities" screen
    Sleep 5000
    SendKeys "%{s}", True
    SendKeys "{v 2}", True
    SendKeys "~", True
    SendKeys "{DOWN 2}", True
    SendKeys "~", True
    Sleep 5000
    SendKeys "^{q}", True
    ' go to "Date" column
    SendKeys "{TAB 2}", True
    SendKeys ">=" & strtDate & " AND " & "<=" & endDate, True
    ' go to the "Position" column
    SendKeys "{TAB 2}", True
    SendKeys Pos & " - " & "BA", True
    ' go to the "Activity Type" column
    SendKeys "{TAB}", True
    SendKeys acType, True
    SendKeys "{TAB 10}", True
    SendKeys "{=}" & catCode, True
    SendKeys "~", True
    'Export
    SendKeys "%{f}", True
    SendKeys "{e 2}", True
    SendKeys "~", True
    SendKeys "{TAB 8}", True
    SendKeys "W:\rcbwpa\RunQueries\VLReports\" & crntDate & "VL Report" & ".csv", True
    SendKeys "~", True
    
    Dim lngCounter As Long
    Dim intFileHwnd As Integer

Do While lngCounter< 6000
    lngCounter = lngCounter + 1
    Sleep 100 'wait for 1/10th second
    If lngCounter Mod 10 = 0 Then
        intFileHwnd = FreeFile
        On Error Resume Next
        Open "W:\rcbwpa\RunQueries\VLReports\" & crntDate & "VL Report" & ".csv" For Append Lock Read Write As #intFileHwnd
        Close #intFileHwnd
        If Err.Number = 0 Then Exit Do
    End If
    DoEvents 'yield processes
Loop

 KillProcess
 
 Sleep 5000
 
 Workbooks.Open Filename:="W:\rcbwpa\RunQueries\VLReports\" & crntDate & "VL Report" & ".csv"
    
    Module2.switchCols
    
    ActiveWorkbook.SaveAs ("W:\rcbwpa\Reports\Individual Center Reports\Fordstar\VehicleLocaterReports\" _
    & myDir & combDate & "VL Report" & ".xls")
    ActiveWindow.Close
    
    Application.DisplayAlerts = False
    ActiveWindow.Close
    Application.DisplayAlerts = False
    Application.Quit


End Sub

Very few parts of this are mine. I only created the parts where it's opening specific files or templates, and where it is saving the final document to. I also had to figure out all the send key information of course, since the example that was posted on this board was for that persons specific setup of Intellitrack. I'm sorry I forgot the persons name who submitted this code, but they used the website of: www.TheScarms.com

I understand what you mean about being able to acess all the systems you need...

Have a good day,

Dave M.

 

[santeria]

Thanks.
I'll have to try this. Looks very useful.

I just spent a day inputting one page of data, or I should say, A half hour or less of manually putting the data in from one set of SAP data, the system I don't have access to to, but everyone else who can't and does not want to use the system, has access to... the rest of the day was spent re-doing my formulas for the project period.

THEN... three quarters of the way through the day, they redefine the time frame of the project, and so more redoing of the formulas.
, for the most part, so long as the totals are done right first, then the rest of it is just a matter of plod through carefully and redo the basic stuff... but it may be basic, but if it is off ( i.e. the extra days on one week, and the additional week not included) then that puts things out. So I had to run some totals out to the right of the sheet so that the figures could be checked.

Back to the figures and tip tapping soon.


Ta

 

[santeria]

Did not hit me until I read the Macro closely... we use Siebel here.
It is about as as handy as Vantive or Remedy from the front end, but the back end is supposed to be great.
Anyway, will be handy if or when I get Siebel back end access.

The other thing is.... Is there a SendKey list ? Is it program specific, like most character mapping that seems to be always different, or is it in some variant on hex code ( like on a THINKPAD, when the Alpha keys go, you have to input hex values for commands to work, and for things to back up).

Ta

 

[Davers]

Heh, Siebel....I hate it!!! We have 27 different departments that we have to query for. If everyone coded things as they were meant to be coded, life would be great! But, that is an employee problem...not a Siebel problem! So far as a sendkey list...I've got one floating around somewhere. I'm fairly certain it is...cross platform...is that the right term? usable. I've used it for excel, Seibel, and Outlook so far...I'll see if I can find where I've hidden my send key file and either post the link if it was a web page, or paste the list of keys.....

Dave M.

 

[Davers]

couldn't find my list anywhere...but excel help has a list under "SendKeys Statement"

Hope this helps....

Dave