... dunque ho questa macro in VBA nativo che legge da un file di tsto dei dati e li insersce in un folglio di excel ed in una tabella di access.
Vorei fare la stessa cosa tramite una pagina ASP...
Naturalmente non è importate inserire i dati in excel
Call DATA_TAB
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", PROVADatabase, adOpenKeyset, adLockOptimistic, adCmdTableDirect
ExportRecordSet.Index = "SERVIZIO"
Set ELENCO = Worksheets("L0785_TOTALE")
CONT = FirstFree("L0785_TOTALE", "A", 6) 'Record iniziale per il riempimento del tabulato
Dim var_DATACONT As Date
Dim var_VAL As Date
iFile = FreeFile()
vFile = ("C:\EPF\L0785.EPF")
Open vFile For Input As #1
While Not EOF(1)
Line Input #1, RIGA
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 EOF(1) Then
Line Input #1, RIGA
var_DESCR = Trim(Mid(RIGA, 10, 36))
var_DESCR2 = Trim(Mid(RIGA, 96, 30))
Line Input #1, RIGA
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
ID = var_CAUS & "-" & var_ABI & "-" & var_CAB & "-" & var_nrass
Set Found_ID = Sheets("L0785_TOTALE").Columns("S:S").Find(ID, LookIn:=xlFormulas)
If Found_ID Is Nothing Then
ELENCO.Range("A" & Trim(Str(CONT))).Value = CStr(var_DATACONT)
ELENCO.Range("B" & Trim(Str(CONT))).Value = var_DIP
ELENCO.Range("C" & Trim(Str(CONT))).Value = var_COD
ELENCO.Range("D" & Trim(Str(CONT))).Value = var_NUMCC
ELENCO.Range("E" & Trim(Str(CONT))).Value = var_NOME
ELENCO.Range("F" & Trim(Str(CONT))).Value = var_CAUS
ELENCO.Range("G" & Trim(Str(CONT))).Value = var_DARE
ELENCO.Range("H" & Trim(Str(CONT))).Value = var_AVERE
ELENCO.Range("I" & Trim(Str(CONT))).Value = CStr(var_VAL)
ELENCO.Range("J" & Trim(Str(CONT))).Value = var_MITT
ELENCO.Range("K" & Trim(Str(CONT))).Value = var_ANOMAL
ELENCO.Range("L" & Trim(Str(CONT))).Value = var_DESCR
Select Case ELENCO.Range("L" & Trim(Str(CONT))).Value
Case "RIMESSA ASSEGNI BANCARI INSOLUTI E P"
ELENCO.Range("L" & Trim(Str(CONT))).Value = "RIM. ASS. INS. PROT."
End Select
ELENCO.Range("M" & Trim(Str(CONT))).Value = var_DESCR2
ELENCO.Range("N" & Trim(Str(CONT))).Value = var_ABI
ELENCO.Range("O" & Trim(Str(CONT))).Value = var_CAB
ELENCO.Range("P" & Trim(Str(CONT))).Value = var_IMP_PAG
Select Case ELENCO.Range("P" & Trim(Str(CONT))).Value
Case "IMPAG.N.ASS.BAN."
ELENCO.Range("P" & Trim(Str(CONT))).Value = "IMPAGATO"
Case "PAGATON.ASS.BAN."
ELENCO.Range("P" & Trim(Str(CONT))).Value = "PAGATO"
Case "IMPAG.N.ASS.CIR."
ELENCO.Range("P" & Trim(Str(CONT))).Value = "IMP. A.C."
End Select
ELENCO.Range("Q" & Trim(Str(CONT))).Value = var_nrass
ELENCO.Range("R" & Trim(Str(CONT))).Value = var_MT
ELENCO.Range("S" & Trim(Str(CONT))).Value = var_CAUS & "-" & var_ABI & "-" & var_CAB & "-" & var_nrass
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
End If
Wend
rs.Close
rs.Open "SELECT Count(SERVIZIO) As Cnt FROM TOTALE", cn, adOpenKeyset, adLockOptimistic, adCmdText
Range("H3") = rs!cnt
ExportRecordSet.Close
Set ExportRecordSet = Nothing
PROVADatabase.Close
Set PROVADatabase = Nothing
Close #1
ActiveWorkbook.Save
Sheets("L0785_TOTALE").Select
Range("A7").Select
Unload UserForm2
Call VER_DATA_TAB
End Sub
Rispondi quotando