Hack sage200C (ini) Utilities

[AngelMiguel]

02-03-2021

1.- Import file CSV "Hacienda Publica" IVA to table

Name: Mdl_ImportCsv

Option Compare Database
Option Explicit

Public Function TableExists(sName As String) As Boolean
TableExists = DCount("*", "MsysObjects", "[Name]= '" & sName & "'")
End Function


Private Sub importameDatosIvaPeriodo()
Dim i As Integer
Dim objStream As Variant
Dim line As String
Dim fso As Object
Dim strSql As String
Dim NaMeTable As String
Dim NombreCampo As String
Dim cadena As String
Dim arr As Variant
Dim SArchivo As String
Dim rst As DAO.Recordset

If TableExists("Tbl_RecibidasIva") Then DoCmd.DeleteObject acTable, ("Tbl_RecibidasIva")

SArchivo = CurrentProject.Path & "\Exportacion_RecibidasZIP.csv"
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(SArchivo) Then
Set objStream = fso.OpenTextFile(SArchivo, 1, False, 0)
End If

NaMeTable = "Tbl_RecibidasIva"

line = objStream.ReadLine
arr = Split(line, ";")
strSql = "CREATE TABLE " & NaMeTable & " ( "

For i = 0 To UBound(arr) - 2
cadena = Nz(Left(Trim(arr(i)), 45), i)
cadena = Replace(cadena, ".", " ", , , vbTextCompare)
NombreCampo = cadena
Select Case i
Case 6 To 90
strSql = strSql & "[" & NombreCampo & "]" & " Double, "
Case Else
strSql = strSql & "[" & NombreCampo & "]" & " TEXT, "
End Select
NombreCampo = ""
Dim sCadena
Next

strSql = strSql & "[" & Nz(Left(Trim(arr(i)), 20), i) & "]" & " " & " TEXT "
strSql = strSql & ")"
DoCmd.SetWarnings (False)
CurrentDb.Execute strSql
DoCmd.SetWarnings (True)

strSql = "": cadena = "": arr = ""

Set rst = CurrentDb.OpenRecordset("Select * from " & NaMeTable)
While Not objStream.AtEndOfStream
line = objStream.ReadLine
arr = Split(line, ";")
With rst
.AddNew
For i = 0 To UBound(arr) - 2
Select Case i
Case 6 To 90
If Len(arr(i)) = 0 Then
cadena = 0
Else
cadena = Nz(Left(Trim(arr(i)), 45), 0)
rst(i) = IIf(Len(cadena) = 0, 0, Replace(cadena, ".", ","))
End If
Case Else
cadena = Nz(Left(Trim(arr(i)), 45), "")
rst(i) = cadena
End Select
Next
.Update
End With
Wend
Set rst = Nothing
Access.RefreshDatabaseWindow
Set fso = Nothing
End Sub


2.- Import tables Sql Sage200c

Private Sub CmdImportIva_Click()

If TableExists("Movimientos") Then DoCmd.DeleteObject acTable, ("Movimientos")
If TableExists("MovimientosIva") Then DoCmd.DeleteObject acTable, ("MovimientosIva")
If TableExists("MovimientosFacturas") Then DoCmd.DeleteObject acTable, ("MovimientosFacturas")

Dim SUid As String: SUid = "NameUsuarioSage200c"
Dim Sclave As String: Sclave = "ClaveSage200c"
Dim Sdatabase As String: Sdatabase = "NameDatabase"
Dim Sserver As String: Sserver = "NameServidor"
DoCmd.TransferDatabase acImport, "ODBC Database", "ODBC;Driver=SQL Server Native Client 11.0;Uid=" & SUid & ";Pwd=" & Sclave & ";LANGUAGE=Español;Server=" & Sserver & ";Database=" & Sdatabase & ";Trusted_Connection=No", acTable, "movimientos", "Movimientos"
DoCmd.TransferDatabase acImport, "ODBC Database", "ODBC;Driver=SQL Server Native Client 11.0;Uid=" & SUid & ";Pwd=" & Sclave & ";LANGUAGE=Español;Server=" & Sserver & ";Database=" & Sdatabase & ";Trusted_Connection=No", acTable, "movimientosFacturas", "MovimientosFacturas"
DoCmd.TransferDatabase acImport, "ODBC Database", "ODBC;Driver=SQL Server Native Client 11.0;Uid=" & SUid & ";Pwd=" & Sclave & ";LANGUAGE=Español;Server=" & Sserver & ";Database=" & Sdatabase & ";Trusted_Connection=No", acTable, "movimientosIva", "MovimientosIva"

End Sub


...continuará