作者: nulry 時間: 2017-3-6 20:50 標題: VBA + SQL
請問VBA 點分初,中, 高級?
小弟都係只學到去 email download files, 出 email 連 attachement, copy and paste 個 d 咁
之前一直都覺得唔識去到太叻, 識睇點去改 d path 就 ok, 但做我呢行, 其實都要識 programming, 所以想去學, 同埋學埋 SQL, 唔知如果想去到 中高級 level 要識 d 乜, 同幾耐時間到呢?
thx all~~
作者: ronstudio 時間: 2017-3-6 22:37
When you have the goal in mind for what to achieve, it'll be easier.
I guess sooner or later you'll move away from from VBA and moving towards other scripting language like Perl, Ruby or Python as they are generally more powerful (having more publicly available modules).
"但做我呢行", so which industry do you work in?
作者: nulry 時間: 2017-3-6 22:49
When you have the goal in mind for what to achieve, it'll be easier.
I guess sooner or later you'll ...
ronstudio 發表於 2017-3-6 22:37
in risk management
作者: nulry 時間: 2017-3-6 22:52
When you have the goal in mind for what to achieve, it'll be easier.
I guess sooner or later you'll ...
ronstudio 發表於 2017-3-6 22:37
in risk management
what means by scripting language? Seems Pyhton is more well known now
作者: happy_99 時間: 2017-3-7 10:41
學SQL 都要睇你用乜野database, 如果你唔係有application 用到既話好難開始到.
作者: myricky 時間: 2017-3-7 16:29
提示: 作者被禁止或刪除 內容自動屏蔽
作者: ronstudio 時間: 2017-3-7 18:13
回覆 4# nulry
I thought R programming lang were more popular in your field
作者: nulry 時間: 2017-3-7 20:48
用Excel vba就行到sql啦 仲可以因應唔同情況將variable寫落sql script好大彈性, run完再用vba抽取data同 ...
myricky 發表於 2017-3-7 16:29
Yes, we are using this~
作者: nulry 時間: 2017-3-7 20:48
回覆 nulry
I thought R programming lang were more popular in your field
ronstudio 發表於 2017-3-7 18:13
it deps, my company didnt use it
作者: myricky 時間: 2017-3-7 22:41
提示: 作者被禁止或刪除 內容自動屏蔽
作者: nulry 時間: 2017-3-7 23:28
咁就得啦, 你睇多D功力深厚同事所寫既VBA, 初初以抄改為主, 再慢慢增加自己寫作比重, 好快上手的. 有咩 ...
myricky 發表於 2017-3-7 22:41
咁既， 佢有 d code 係落 SQL. 之後有 d 就由 database落番 excel.
database 落番 excel 既 VBA 大概識去改, 但 VBA 落 databse, 好難咁....
作者: myricky 時間: 2017-3-8 09:56
提示: 作者被禁止或刪除 內容自動屏蔽
作者: nulry 時間: 2017-3-8 22:01
你講經sql update落個database table好難? 其實都係update姐, 不過我勸你係未熟sql / database (ERP)結構 ...
myricky 發表於 2017-3-8 09:56
因為 IT d 人唔識 programming, 而做 risk 要用有 database, 我 manager 就自己寫
師兄, 當我 SQL 係入門, 有無咩書 or 邊條 youtube 可以 reference?
VBA 又有無好既 share? thx~~
作者: seasonpp 時間: 2017-3-9 22:23
因為 IT d 人唔識 programming, 而做 risk 要用有 database, 我 manager 就自己寫
師兄, 當我 SQL 係入 ...
nulry 發表於 2017-3-8 22:01
你個database係咩database? Access? MSSQL? MySQL?
作者: happy_99 時間: 2017-3-10 12:51
用Excel vba就行到sql啦 仲可以因應唔同情況將variable寫落sql script好大彈性, run完再用vba抽取data同 ...
myricky 發表於 2017-3-7 16:29
當然可以, 我都會用Excel VBA 抽data. 問題係單一個excel connect 去 sql server同oracle db 都會有D唔同. 除非真係學好基本既select statement, 做下加減做, summary.
同埋用得excel VBA, 不如轉用 access + VBA仲完整D. 開完個table, 有UI 幫手砌query, input interface到出report. 相對係易D做到出黎, 同全面D.
作者: myricky 時間: 2017-3-10 13:03
提示: 作者被禁止或刪除 內容自動屏蔽
作者: nulry 時間: 2017-3-10 22:40
你個database係咩database? Access? MSSQL? MySQL?
seasonpp 發表於 2017-3-9 22:23
Access
作者: nulry 時間: 2017-3-10 22:42
>>問題係單一個excel connect 去 sql server同oracle db 都會有D唔同
你意思係connection syntax唔同, 定 ...
myricky 發表於 2017-3-10 13:03
其實 Access 去 excel, excel 落 Access, 係咪都係用黎用去個句幾 lang. 然後只揀番邊個 database, 邊張 querry, 同要 d 咩 filed 就可以?
作者: travelntravel 時間: 2017-3-29 13:35
我識SQL, 而家研究緊VBA, Are there anywhere has tutorial on running SQL in VBA? 我諗咁對我來講會易好多
作者: myricky 時間: 2017-3-30 16:47
提示: 作者被禁止或刪除 內容自動屏蔽
作者: nulry 時間: 2017-3-30 22:22
本帖最後由 nulry 於 2017-3-30 23:33 編輯
http://accessjitsu.com/2015/09/1 ... an-access-database/
反而我唔知點去改 SQL d coding
Set db = OpenDatabase(DbLoc)
SQL = "SELECT SalesQuarter & '-' & SalesYear, Division, GrossSales, GrossMargin "
SQL = SQL & "FROM QuarterlySales "
SQL = SQL & "WHERE SalesQuarter IN (1, 2) "
SQL = SQL & "ORDER BY SalesYear, SalesQuarter "
點可以改成
SQL = SELECT A, B From "C table", where A="XXXX" and B = "NNNN" Order by C ?
作者: myricky 時間: 2017-3-31 11:33
提示: 作者被禁止或刪除 內容自動屏蔽
作者: edwardngc 時間: 2017-3-31 15:40
本帖最後由 edwardngc 於 2017-3-31 15:46 編輯
我識SQL, 而家研究緊VBA, Are there anywhere has tutorial on running SQL in VBA? 我諗咁對我來講會易好 ...
travelntravel 發表於 2017-3-29 13:35
錄MARCO後改嘢係最易GE方法.
IN EXCEL (I'M USING 2010) DATA -> FORM OTHER SOURCES -> MS QUERY, 選DATA SOURCE, 之後寫好條SQL, 再RETURN DATA TO EXCEL. ->STOP MARCO RECORD.
你會錄倒類似以下ge東西,咁你可以晌個VBA EDITOR 度改嘢自用.
Sub Macro1()
'
' Macro1 Macro
'
'
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
"ODBC;DSN=ABCDE;Description=CY142;UID=ABCDE;PWD=ABCDE;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=ABCDE;DATABASE=ABCDE;LANGUA" _
), Array("GE=us_english")), Destination:=Range("$A$3")).QueryTable
.CommandText = Array("SELECT * FROM ABCDE;")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_Query_from_ABCDE"
.Refresh BackgroundQuery:=False
End With
End Sub
作者: nulry 時間: 2017-3-31 20:32
回覆 nulry
SELTEXT="SELECT A, B "
FROMTEXT ="FROM C TABLE "
WHERETEXT = "WHERE A='XXX' AND B='NN ...
myricky 發表於 2017-3-31 11:33
ching, 係咪肯定咁入 ok?
有 error..
作者: myricky 時間: 2017-3-31 22:22
提示: 作者被禁止或刪除 內容自動屏蔽
作者: nulry 時間: 2017-3-31 23:00
肯定係...............除非唔係...
講笑... 我一直都係按呢類分段式varible去改sql, 會比較清 ...
myricky 發表於 2017-3-31 22:22
我發覺我只可以用 SELECT * 先出到野, 但會係成個 tablet, 我揀 columns, 會話 無值提供或多一個參數
Set Rs = .Execute(sSQL) >>> 停係呢到>..
作者: myricky 時間: 2017-3-31 23:08
提示: 作者被禁止或刪除 內容自動屏蔽
作者: nulry 時間: 2017-3-31 23:11
Code 1:
Option Explicit
Sub Access_Data()
'Requires reference to Microsoft ActiveX Data Objects xx Library
Dim Cn As ADODB.Connection, Rs As ADODB.Recordset
Dim MyConn, sSQL As String
Dim SelectText, FromText, WhereText, OrderText As String
Dim Rw As Long, Col As Long, c As Long
Dim MyField, Location As Range
'Set destination
Set Location = [A5]
'Set source
MyConn = "C:\Users\nulry\Documents\Access2010DB\ProfsrFinancial.accdb"
'Create query
SelectText = "SELECT BasicSalesQuery2 & '-' & Rep.name, "
FromText = "FROM BasicSalesQuery2 "
WhereText = ""
OrderText = ""
sSQL = SelectText + FromText + OrderText
MsgBox (sSQL)
'Create RecordSet
Set Cn = New ADODB.Connection
With Cn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Open MyConn
Set Rs = .Execute(sSQL)
End With
'Write RecordSet to results area
Rw = Location.Row
Col = Location.Column
c = Col
Do Until Rs.EOF
For Each MyField In Rs.Fields
Cells(Rw, c) = MyField
c = c + 1
Next MyField
Rs.MoveNext
Rw = Rw + 1
c = Col
Loop
Set Location = Nothing
Set Cn = Nothing
End Sub
作者: nulry 時間: 2017-3-31 23:12
'Create query
SelectText = "SELECT Rep.name "
FromText = "FROM BasicSalesQuery2 "
WhereText = ""
OrderText = ""
sSQL = SelectText + FromText + OrderText
MsgBox (sSQL)
作者: nulry 時間: 2017-3-31 23:13
你select *出到野即係其他部份冇問題, 只係係select field(唔係叫column)部份出錯. 咁你post select部份 ...
myricky 發表於 2017-3-31 23:08
'Create query
SelectText = "SELECT Rep.name "
FromText = "FROM BasicSalesQuery2 "
WhereText = ""
OrderText = ""
sSQL = SelectText + FromText + OrderText
MsgBox (sSQL)
作者: nulry 時間: 2017-3-31 23:43
你select *出到野即係其他部份冇問題, 只係係select field(唔係叫column)部份出錯. 咁你post select部份 ...
myricky 發表於 2017-3-31 23:08
ching, 唔該哂， 知道咩問題，係 table 入面, 最頂個行, 叫 A, 但原來係最 raw 既 data 係叫 aB, 所以 call 唔到.~~
作者: myricky 時間: 2017-4-1 00:36
提示: 作者被禁止或刪除 內容自動屏蔽
作者: nulry 時間: 2017-4-1 09:43
SelectText = "SELECT Rep.name "
FromText = "FROM BasicSalesQuery2 "
WhereText = ""
Or ...
myricky 發表於 2017-4-1 00:36
會唔會係BasicSalesQuery2係一個query table, 而Rep係data table? 而係只係想要Rep table入面'name'呢個field?
係呢個， 所以唔得～～ 唔好意思， 唔太熟 SQL
thx~~
作者: nulry 時間: 2017-4-1 09:44
SelectText = "SELECT Rep.name "
FromText = "FROM BasicSalesQuery2 "
WhereText = ""
Or ...
myricky 發表於 2017-4-1 00:36
ching 呢家攞 data 既 VBA 搞好左, 咁要 feed 去 database 呢?
我睇番我公司, manger 寫個 d 太覆雜......睇唔明, 你有無 d coding 較 easy?
作者: nulry 時間: 2017-4-3 23:38
揾到 export Excel data to database coding
Option Explicit
Sub ADOXLtoSQLSRV()
Dim cn As ADODB.Connection
Dim strConn As String
Dim strSQL As String
Dim strXLSource As String
Dim lngRecsAff As Long
strConn = strConn & "Provider=SQLOLEDB;Data Source=BIG-TOSH;"
strConn = strConn & "Initial Catolog=Customers;Trusted_Connection=YES"
Set cn = New ADODB.Connection
cn.Open strConn
strXLSource = "C:\\AccountNos.xls;Extended Properties=Excel 12.0"
strSQL = " INSERT INTO Customers.dbo.XLImport "
strSQL = strSQL & " ([Account]) "
strSQL = strSQL & " SELECT [Account] "
strSQL = strSQL & " FROM "
strSQL = strSQL & " OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source=" & strXLSource & "')...[tblAccounts$] "
Debug.Print strSQL
cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
Debug.Print "Records affected: " & lngRecsAff
cn.Close
End Sub
-------------------------
但好似見唔到由 sheet 既邊個 range 開始....
有無師兄有 ideas/
作者: 孤寂使者 時間: 2017-4-5 06:45
VBA + SQ點樣易入門呀?有速成大法
作者: nulry 時間: 2017-4-6 22:39
Hi all~~
試到了， 有 隻簡單 d 既 coding
Public Sub DoTrans2()
Dim i, j, m, n As Integer
Dim sh1 As Worksheet
Dim db(1 To 1000, 1 To 1000) As Variant
Dim dbWb, dbWs As String
Set sh1 = ThisWorkbook.ActiveSheet
Set Cn = CreateObject("ADODB.Connection")
dbPath = sh1.Cells(3, 2)
dbWb = Application.ActiveWorkbook.FullName
dbWs = Application.ActiveSheet.Name
scn = "Provider='Microsoft.ACE.OLEDB.12.0';Data Source=" & dbPath
dsh = "[" & Application.ActiveSheet.Name & "$]"
Cn.Open scn
For i = 7 To 8
sSQL = "INSERT INTO abc Values ('" & Cells(i, 1) & "' ," & Cells(i, 2) & " ) "
Cn.Execute sSQL
Next
End Sub
作者: nulry 時間: 2017-4-6 22:41
但有個問題係， 我唔識熟點去 create database
我 feed 去 ms access 個時, 佢會話我個 key index dupciate, 因為我其求揾 d data 去試, e.g. abc, 123...
咁係咪我最好用 date 去做一個 key index, 咁其他 filed 就算重覆左都無問題?