×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

"Bad file.." when creating interface file with Macro-derived Fields

"Bad file.." when creating interface file with Macro-derived Fields

"Bad file.." when creating interface file with Macro-derived Fields

(OP)
Hello hello,

I  went to run a macro to produce a csv file like normal and I got a "Bad File name or number" error, and the file was only created with the headers.

the macro is as follows:

CODE

Option Explicit
Sub ProbToCSV()

Dim today as string
today = format$(Date$, "medium date")

Dim Filename as string
Filename = "C:\HRDocuments\Probation-(" + today + ").csv"
'Filename = "C:\Documents\HEU\Probation\Probation-(" + today + ").csv"

Dim FileNumber as Double
FileNumber = FreeFile

Open FileName For Output ACCESS Write Lock Read Write as FileNumber

Print #FileNumber,"Last Name,FirstName,Type,Position,Probation Status,Hours Worked,Hours Remaining,Days Remaining,PayRateCheck,Pay Rate,BenefitCheck,MSP,GroupHealth"

GetRandom 1

Dim i as integer
Dim FileNum as string
For i=1 to TotalRecords

   If FileNum <> Field$("FILE#") Then

      Print #FileNumber,Field$("LASTNAME")+","+Field$("FIRSTNAME")+","+Field$("EMPLOYEETYPE")+","+Field$("Pos")+","+Field$("Probation")+","+SumField$("Totalwkdhrs","Derived Field",1,"Sum")+","+Field$("ProbtnHrsRemain")+","+Field$("DaysServiceRemain")+","+Field$("IncreaseCheck")+","+Field$("RATE1AMT")+","+Field$("Benefits")+","+Field$("MSP")+","+Field$("GroupHealth")

      FileNum = Field$("FILE#")
   
   End If

   GetNext

Next i

End Sub

When I change the "Print #FileNumber..." line that is within the conditional to just:

Print #FileNumber,Field$("LASTNAME")

.. I don't get the error, but the file is just headers followed by a single last name.


The fields "Probation", "ProbtnHrsRemain", "DaysServiceRemain", "IncreaseCheck", and "Benefits" are all macro-derived.

Here is an example ("Probation"):

CODE

Option Explicit
Sub Probation()

Dim EEType as string
EEType = Field$("EMPLOYEETYPE")

Dim Today as variant
Today = DateValue(Date$)

Dim HireDt
HireDt = DateValue(Field$("HIREDATEROE"))

Dim DaysService
DaysService = today - HireDt

Dim hours
hours = Val(SumField$("Totalwkdhrs","Derived Field",1,"Sum"))

If hours > 480 or DaysService > 180 Then

          DerivedField "Past Probation"

Else
     If EEType <> "C" Then

          If DaysService > 90 Then
               DerivedField "Past if working in Line for 3 months"
          ElseIf hours >400 Then
               DerivedField "Approaching Probation End"
          Else
               DerivedField "Probationary"
          End If

     ElseIf EEType = "C" Then

         If  DaysService > 160 or hours > 440 Then
              DerivedField "Approaching Probation End"
         Else
              DerivedField "Probationary"
         End If

     End If

End If

End Sub

Any ideas would be much appreciated! Thank you!

RE: "Bad file.." when creating interface file with Macro-derived Fields

Add the fields back one at a time and see if you can narrow it down a bit.

http://CharlesCook.com
Specializing in ReportSmith Training and Consulting

RE: "Bad file.." when creating interface file with Macro-derived Fields

(OP)
Hi Charles,

I went through them all, and the only fields that cause the error are Macro-derived fields, any of them.

I tried Printing a macro-derived field result outside of the loop, right after the "GetRandom 1", and it gave the same error.

Are you supposed to be able to deal with macro-derived field results in macros?


I managed to figure out the looping problem, and changed "FILE#" to "REPORTS.V_EMPLOYEE.FILE#". This did not help the error :(

Thanks for any and all help!

RE: "Bad file.." when creating interface file with Macro-derived Fields

do the macro derived fields look ok in the report itself?

http://CharlesCook.com
Specializing in ReportSmith Training and Consulting

RE: "Bad file.." when creating interface file with Macro-derived Fields

(OP)
They look perfect. Drat.

I removed "#FileNumber" from the print command within the conditional. The output to the screen was perfect!

I removed the "#" and got perfect output to the screen, with a "1" at the beginning of every record.

I tried replacing the "FileNumber" variable with "1", and got the same error.

For whatever reason, it seems like it just doesn't want to give my macro-derived field results to the file. Is there some setting in the application that needs to allow this? I'm grasping at straws now...

Thank you!

RE: "Bad file.." when creating interface file with Macro-derived Fields

I have been working with ReportSmith since 1994 version 1.2(ish) and I don't think I have ever tried this.  I always use SQL Derived Fields to do this.

This is what I tried...

My Macro Derived Field:

Sub simple()
    DerivedField "CharlesCook.com"
End Sub

My Export Macro:

Sub ProbToCSV()
     FileNumber = FreeFile
     FileName = "U:\test.csv"
     Open FileName For Output Access Write Lock Read Write  As FileNumber
     GetRandom 1
     For i=1 to 100

          Rem This works
          'print #FileNumber,Field$("EMPLID")

          Rem This does not
          'print #FileNumber,Field$("EMPLID"),Field$("simple")

          GetNext
     Next i
End Sub

Can you convert your report to use SQL Derived Fields?

Have you talked to ADP Support?

Sorry I can't be more helpfull.

http://CharlesCook.com
Specializing in ReportSmith Training and Consulting

RE: "Bad file.." when creating interface file with Macro-derived Fields

(OP)
Thanks for looking into it Charles! What a shame!

The Second big code block in my first post is an example of one of the macro derived fields, some are more complex. I probably should learn SQL well-enough to do the translation, sometime...

I called ADP, and, no surprise, they do not support this feature of ReportSmith!

Maybe I'll just spend the time to align the fields properly and do a "SaveReport Filename, 5" I'd really rather not put the code of each macro-derived field into the conditional..


Thanks again!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close