Results 1 to 5 of 5
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    trasnform macro excel in asp page

    is possible:
    (tested with my great Hans)
    assuming the mdb and the txt file are located in:c:inetpubwwwwrootdb

    code------------
    Dim var_DATACONT As Date
    Dim var_VAL As Date
    Dim fso As Object
    Dim fil As Object
    Dim ExportRecordSet As ADODB.Recordset
    Dim PROVADatabase As ADODB.Connection
    Set PROVADatabase = New ADODB.Connection
    PROVADatabase.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source='" & gPROVADatabasePath & "'; User Id=admin; Password=;"

    Set ExportRecordSet = New ADODB.Recordset
    ExportRecordSet.Open "TOTALE_NO_EXCEL", PROVADatabase, adOpenKeyset, adLockOptimistic, adCmdTableDirect
    ExportRecordSet.Index = "SERVIZIO"

    ' Create the filesystemobject and open the text file
    Set fso = CreateObject("Scripting.FileSystemObject")
    vFile = "C:EPFL0785.txt"
    Set fil = fso.OpenTextFile(vFile)

    ' Loop through the lines of the text file
    Do While Not fil.AtEndOfStream
    ' Read a line into RIGA
    RIGA = fil.ReadLine
    ' Code to process the line goes here

    If Len(Trim(RIGA)) > 0 Then
    If InStr(Mid(RIGA, 1, 12), "DATA CONTAB.") > 0 Then
    var_DATACONT = Mid(RIGA, 15, 10)
    End If
    If InStr(Mid(RIGA, 1, 11), "DIPENDENZA:") > 0 Then
    var_DIP = Format(Mid(RIGA, 14, 4), "#00000")
    End If
    If InStr(Mid(RIGA, 5, 1), "-") > 0 And InStr(Mid(RIGA, 13, 1), "-") > 0 Then
    var_COD = Mid(RIGA, 1, 3)
    End If

    If var_COD = "442" Then 'NEW LINE CONTROLLO SOLO SU UN CODICE BATCH

    If InStr(Mid(RIGA, 50, 2), "55") Or InStr(Mid(RIGA, 50, 2), "36") > 0 And InStr(Mid(RIGA, 98, 1), "/") > 0 Then
    var_NUMCC = Trim(Mid(RIGA, 1, 6))
    var_NOME = Trim(Mid(RIGA, 8, 40))
    var_CAUS = Mid(RIGA, 50, 2)

    var_DARE = Trim(Mid(RIGA, 60, 14))
    var_AVERE = Trim(Mid(RIGA, 80, 14))
    var_VAL = Mid(RIGA, 96, 10)
    var_MITT = Format(Mid(RIGA, 107, 4), "#00000")
    var_ANOMAL = Trim(Mid(RIGA, 116, 10))

    If Not fil.AtEndOfStream Then

    RIGA = fil.ReadLine

    var_DESCR = Trim(Mid(RIGA, 10, 36))
    var_DESCR2 = Trim(Mid(RIGA, 96, 30))

    RIGA = fil.ReadLine

    var_ABI = Format(Mid(RIGA, 17, 5), "#00000")
    var_CAB = Format(Mid(RIGA, 23, 5), "#00000")
    var_IMP_PAG = Trim(Mid(RIGA, 29, 16))
    var_nrass = Val(Trim(Mid(RIGA, 45, 10))) * 1
    var_MT = Trim(Mid(RIGA, 56, 4))

    End If

    CONT = CONT + 1

    If Not ExportRecordSet.BOF Then
    ExportRecordSet.MoveFirst
    End If
    ExportRecordSet.Seek var_CAUS & "-" & var_ABI & "-" & var_CAB & "-" & var_nrass, adSeekFirstEQ
    If ExportRecordSet.EOF = True Then
    With ExportRecordSet

    .AddNew
    .Fields("DATA_CONT") = CStr(var_DATACONT)
    .Fields("DIP") = Format((var_DIP), "#0000")
    .Fields("COD_BATCH") = var_COD
    .Fields("C_C") = Format((var_NUMCC), "#0")
    .Fields("NOMINATIVO") = var_NOME
    .Fields("CAUS") = var_CAUS
    .Fields("DARE") = var_DARE
    .Fields("AVERE") = var_AVERE
    .Fields("VAL") = CStr(var_VAL)
    .Fields("SPORT_MIT") = Format((var_MITT), "#0000")
    .Fields("ANOM") = var_ANOMAL
    .Fields("DESCR") = var_DESCR
    Select Case .Fields("DESCR")
    Case "RIMESSA ASSEGNI BANCARI INSOLUTI E P"
    .Fields("DESCR") = "RIM. ASS. INS. PROT."
    End Select
    .Fields("CRO") = var_DESCR2
    .Fields("ABI") = var_ABI
    .Fields("CAB") = var_CAB
    .Fields("PAG_IMP") = var_IMP_PAG

    Select Case .Fields("PAG_IMP")
    Case "IMPAG.N.ASS.BAN."
    .Fields("PAG_IMP") = "IMPAGATO"
    Case "PAGATON.ASS.BAN."
    .Fields("PAG_IMP") = "PAGATO"
    Case "IMPAG.N.ASS.CIR."
    .Fields("PAG_IMP") = "IMP. A.C."
    End Select

    .Fields("NR_ASS") = var_nrass
    .Fields("MT") = var_MT
    .Fields("SERVIZIO") = var_CAUS & "-" & var_ABI & "-" & var_CAB & "-" & var_nrass
    .Update
    End With
    End If

    End If
    End If
    End If

    Loop

    ExportRecordSet.Close
    Set ExportRecordSet = Nothing
    PROVADatabase.Close
    Set PROVADatabase = Nothing

    ' Close the text file and clear the variables
    fil.Close
    Set fil = Nothing
    Set fso = Nothing

    End Sub

    code------------

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: trasnform macro excel in asp page

    The VBScript code that one usually uses in an ASP page is very similar to VBA. The main things to change include your variable declarations and your paths. Do you have anyone there who is familiar with ASP who can work with you on converting your code?

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: trasnform macro excel in asp page

    Tks jscher2000, for help but i am not a good exepert with ASP, if you wnat to help me...
    Tell the minimal suggestion to transform the macro in ASP, or good is to correct you;-)
    Sorry for my english
    Goos is if i attch the mdb and txt file?

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: trasnform macro excel in asp page

    Sorry, no, I don't have a server on which I can do ASP development (except for my internal projects).

    To fix your variable declarations remove the "As Type" because in VBScript, everything is a variant.

    To fix your database path (gPROVADatabasePath), you can use the Server.MapPath method to convert your relative URL (e.g., db/mydata.mdb) into an absolute path that the ADO DLL will be able to find.

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: trasnform macro excel in asp page

    Good and tks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •