codice:
Dim sExcelPath, sdbPath, sLog As String
sExcelPath = System.IO.Path.GetDirectoryName(Application.ExecutablePath)
sdbPath = "C:\db_From"
sLog = sExcelPath & "\logs"
Dim sFIle As System.IO.StreamWriter
sFIle = System.IO.File.CreateText(sLog & "\" & Date.Now.DayOfYear & ".txt")
' Add some information to the file.
sFIle.WriteLine(DateTime.Now.TimeOfDay.ToString & "Creazione del db")
Dim tblmy As DataTable
Dim oledbParadoxconn As New OleDb.OleDbConnection
'EXCEL######################################
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
'variabili memmorizazione dalla query#############################################
Dim ShopCOde, DelDate, StockCode, ShrtDesc, WEBDESC, BRAND, VarDesc, Varcode, VarAltCode, PurchCost, Purchvat, UnitPrice, PriceVat, Desc As String
Dim DescFrom, DescTo, AdoCOst1, AdoCost2, InvtryDate, Stock, Gruppo, sGruppo, TMisura, GMisura, Misura, Stagione, Anno, Sesso, Colore, Materiale, Box, PICFile As String
'COSTANTI#####################################
ShopCOde = "011"
DelDate = DateTime.Now.Date
Try
sFIle.WriteLine(DateTime.Now.TimeOfDay.ToString & "Inizio query db paradox")
lblProc.Text = "Query database Paradox"
Dim Sqltext As String = "SELECT A.[Articolo Fornitore], A.Descrizione, A.MARCA, A.[ARTICOLO ETICHETTA], A.[CATEGORIA MERCEOLOGICA], A.[TIPO ARTICOLO], V.[DENOMINAZIONE VARIANTE 1], L.[PREZZO IVATO E], G.[DATA ULTIMO MOVIMENTO], G.[QTA INIZIALE]-G.[QTA SCARICO]+G.[QTA CARICO] AS TOT,U.UBICAZIONE, V.[VARIANTE 2], V.[VARIANTE 1] FROM ARTICOLI A, ARTBASE V, UBICAZIONE U, GIACENZA G,LISTINO L WHERE V.[ARTICOLO INTERNO]=A.[ARTICOLO] AND A.[UBICAZIONE]=U.[CODICE] AND G.[ARTICOLO]=V.[ARTICOLO ETICHETTA] AND L.[ARTICOLO]=A.[ARTICOLO]"
Dim oledbconnstr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sdbPath & ";Extended Properties=Paradox 5.x;"
oledbParadoxconn.ConnectionString = oledbconnstr
oledbParadoxconn.Open()
Dim myDataAdapter As New OleDb.OleDbDataAdapter(Sqltext, oledbParadoxconn)
Dim dtgetdata As New DataSet
myDataAdapter.Fill(dtgetdata, "aaaa")
' Me.dataGrid1.DataSource = dtgetdata
' Me.dataGrid1.DataMember = "aaaa"
tblmy = dtgetdata.Tables("aaaa")
'#####COPIARE DALLA COPIA AL NUOVO
'backupXLS.xls -copia
'fogl.xls = nuovo
'cancella file se esiste già
MsgBox("fine query")
sFIle.WriteLine(DateTime.Now.TimeOfDay.ToString & "fine della query")
If System.IO.File.Exists(sExcelPath & "\fogl.xls") Then System.IO.File.Delete(sExcelPath & "\fogl.xls")
System.IO.File.Copy(sExcelPath & "\backupXLS.xls", sExcelPath & "\fogl.xls")
'EXCEL ##################
xlApp = New Excel.Application
xlWorkBook = xlApp.Workbooks.Open(sExcelPath & "\fogl.xls")
xlWorkSheet = xlWorkBook.Worksheets("Foglio1")
MsgBox("fine copia xls")
'Progress bar###########################################
Progress.Maximum = tblmy.Rows.Count
Dim i As Integer = 0
lblProc.Text = "Inserimento nel file di EXCEL i dati"
sFIle.WriteLine(DateTime.Now.TimeOfDay.ToString & "fine della copia del file excel - inizio della scrittura nel file excel")
WEBDESC = ""
PurchCost = "0"
Purchvat = "0"
PriceVat = "20"
Desc = "0"
DescFrom = ""
DescTo = ""
AdoCOst1 = "0"
AdoCost2 = "0"
Materiale = ""
Box = ""
PICFile = ""
MsgBox(tblmy.Rows.Count)
For Each x In tblmy.Rows
StockCode = x("Articolo Fornitore")
ShrtDesc = x("Descrizione")
BRAND = x("Marca")
VarDesc = ShrtDesc & " " & x("Variante 2")
Varcode = StockCode & "." & x("Variante 2")
VarAltCode = x("Articolo etichetta")
UnitPrice = x("PREZZO IVATO E")
InvtryDate = x("DATA ULTIMO MOVIMENTO")
Stock = x("TOT")
Gruppo = x("CATEGORIA MERCEOLOGICA")
sGruppo = x("Tipo Articolo")
If IsDBNull(x("Denominazione Variante 1")) Then
TMisura = ""
Else
TMisura = x("Denominazione Variante 1")
End If
GMisura = x("TIPO ARTICOLO")
If IsDBNull(x("Variante 1")) Then
Misura = "UNICA"
Else
Misura = x("Variante 1")
End If
Stagione = x("UBICAZIONE")
Anno = Stagione
Sesso = Gruppo
If IsDBNull(x("Variante 2")) Then
Colore = "."
Else
Colore = x("Variante 2").ToString
End If
'SCrivi sul foglio Excel
xlWorkSheet.Cells(i + 2, 1).value = ShopCOde
xlWorkSheet.Cells(i + 2, 2).value = DelDate
xlWorkSheet.Cells(i + 2, 3).value = StockCode
xlWorkSheet.Cells(i + 2, 4).value = ShrtDesc
xlWorkSheet.Cells(i + 2, 5).value = WEBDESC
xlWorkSheet.Cells(i + 2, 6).value = BRAND
xlWorkSheet.Cells(i + 2, 7).value = VarDesc
xlWorkSheet.Cells(i + 2, 8).value = Varcode
xlWorkSheet.Cells(i + 2, 9).value = VarAltCode
xlWorkSheet.Cells(i + 2, 10).value = PurchCost
xlWorkSheet.Cells(i + 2, 11).value = Purchvat
xlWorkSheet.Cells(i + 2, 12).value = UnitPrice
xlWorkSheet.Cells(i + 2, 13).value = PriceVat
xlWorkSheet.Cells(i + 2, 14).value = Desc
xlWorkSheet.Cells(i + 2, 15).value = DescFrom
xlWorkSheet.Cells(i + 2, 16).value = DescTo
xlWorkSheet.Cells(i + 2, 17).value = AdoCOst1
xlWorkSheet.Cells(i + 2, 18).value = AdoCost2
xlWorkSheet.Cells(i + 2, 19).value = InvtryDate
xlWorkSheet.Cells(i + 2, 20).value = Stock
xlWorkSheet.Cells(i + 2, 21).value = Gruppo
xlWorkSheet.Cells(i + 2, 22).value = sGruppo
xlWorkSheet.Cells(i + 2, 23).value = TMisura
xlWorkSheet.Cells(i + 2, 24).value = GMisura
xlWorkSheet.Cells(i + 2, 25).value = Misura
xlWorkSheet.Cells(i + 2, 26).value = Stagione
xlWorkSheet.Cells(i + 2, 27).value = Anno
xlWorkSheet.Cells(i + 2, 28).value = Sesso
xlWorkSheet.Cells(i + 2, 29).value = Colore
xlWorkSheet.Cells(i + 2, 30).value = Materiale
xlWorkSheet.Cells(i + 2, 31).value = Box
xlWorkSheet.Cells(i + 2, 32).value = PICFile
'indicazione
Progress.Value = i
i += 1
Next
sFIle.WriteLine(DateTime.Now.TimeOfDay.ToString & "fine della scrittura file excel")
'chiudi foglio EXCEL#####################################
xlWorkBook.Close()
xlApp.Quit()
releaseObject(xlApp)
releaseObject(xlWorkBook)
releaseObject(xlWorkSheet)
lblProc.Text = "Fine Inserimento - Inizion trasfero dati FTP"
Rispondi quotando