Prikazi cijelu temu 18.10.2010 21:55
zxz Van mreze
Administrator
Registrovan od:03.02.2009
Lokacija:Tuzla


Predmet:Re: Prenos podatak VB-a kodom iz Access-a u Excel
Ovih par dana sam u poslu do guse pa ne stizem nista.
PreuzmiIzvorni kôd (vbnet):
  1. Private Sub Workbook_Open()
  2. Dim db As database
  3. Dim Rs As Recordset
  4. Dim Dokument As Document
  5. Dim Vork As Worksheet
  6. Dim Putanja As String
  7. Dim I As Integer, N As Integer, M As Integer, J As Integer, BrojMagacina As Integer
  8. Dim Podatak, SifraMagacina() As String, SifraArt As String, SQL As String
  9.  
  10.  
  11. Putanja = Me.Path
  12. SQL = "SELECT tskladiste.sif_pj " _
  13.        & "FROM tskladiste " _
  14.        & "GROUP BY tskladiste.sif_pj " _
  15.        & "ORDER BY tskladiste.sif_pj"
  16. Set Vork = Me.Worksheets("Evidencija")
  17. Vork.Cells(7, 1) = "Artikal"
  18. Set db = OpenDatabase(Putanja & "\primjer.mdb")
  19. Set Rs = db.OpenRecordset(SQL)
  20. N = Rs.RecordCount
  21. ReDim SifraMagacina(1 To N) As String
  22. For I = 1 To N
  23. SifraMagacina(I) = Rs.Fields(0)
  24. Next I
  25.  
  26.  
  27. For I = 1 To N * 4 Step 4
  28. Vork.Cells(7, I + 1) = "Cijena"
  29. Vork.Cells(7, I + 2) = "Ulaz"
  30. Vork.Cells(7, I + 3) = "Izlaz"
  31. Vork.Cells(7, I + 4) = "Stanje"
  32. If I = 1 Then
  33. Vork.Cells(6, I) = "'" & Rs.Fields(0)
  34. Vork.Cells(3, I + 1) = "Krinus veleprodaja"
  35. Else
  36. Vork.Cells(6, I + 1) = "'" & Rs.Fields(0)
  37. Vork.Cells(3, I + 1) = "Krinus Prodavnica " & Val(Rs.Fields(0)) - 1
  38. End If
  39. Rs.MoveNext
  40. Next I
  41. Rs.Close
  42. SQL = "SELECT tskladiste.Sifart " _
  43.       & "FROM tskladiste " _
  44.       & "GROUP BY tskladiste.Sifart"
  45. Set Rs = db.OpenRecordset(SQL)
  46. N = Rs.RecordCount
  47. For I = 8 To N + 7
  48. Vork.Cells(I, 1) = "'" & Rs.Fields(0)
  49. Rs.MoveNext
  50. Next I
  51. Rs.Close
  52. For I = 1 To N
  53. SifraArt = Vork.Cells(I + 7, 1)
  54. SQL = "SELECT * FROM QIzlaz_Exel WHERE Sifart='" & SifraArt & "'"
  55. Set Rs = db.OpenRecordset(SQL)
  56. M = Rs.RecordCount
  57. If M > 5 Then
  58. MsgBox "Sranje"
  59. End If
  60.     For J = 1 To M
  61.     BrojMagacina = (Val(Rs!sif_pj) - 1) * 4
  62.     Podatak = Rs.Fields(1)
  63.     Vork.Cells(I + 7, BrojMagacina + 2) = Podatak
  64.     Podatak = Rs.Fields(2)
  65.     Vork.Cells(I + 7, BrojMagacina + 3) = Podatak
  66.     Podatak = Rs.Fields(3)
  67.     Vork.Cells(I + 7, BrojMagacina + 4) = Podatak
  68.     Podatak = Rs.Fields(4)
  69.     Vork.Cells(I + 7, BrojMagacina + 5) = Podatak
  70.     Rs.MoveNext
  71.     Next J
  72. Next I
  73. End Sub

Ovo je kod u exelu umjesto onoga.
Evo i SQl:Qizlaz_exel
PreuzmiIzvorni kôd (SQL):
  1. SELECT tskladiste.Sifart, LAST(IIf([sif_pj]="0001",[cjena],[m_cijena])) AS CijenaP, SUM(tskladiste.kolicina_ulaz) AS SumOfkolicina_ulaz, SUM(tskladiste.kolicina_izlaz) AS SumOfkolicina_izlaz, SUM([Kolicina_Ulaz]-[Kolicina_izlaz]) AS Stanje, tskladiste.sif_pj, COUNT(tskladiste.Sifart) AS CountOfSifart
  2. FROM tskladiste
  3. GROUP BY tskladiste.Sifart, tskladiste.sif_pj
  4. ORDER BY tskladiste.sif_pj;
Podrška samo putem foruma, jer samo tako i ostali imaju koristi od toga.