Created
April 8, 2011 08:58
-
-
Save paranoiq/909520 to your computer and use it in GitHub Desktop.
sada funkcí pro skládání SQL dotazů v Excelu
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| sada funkcí pro skládání SQL dotazů v Excelu | |
| autor: Vlasta Neubauer [@paranoiq] | |
| licence: public domain | |
| pro koho to je: | |
| - pro každého, kdo musí ručně upravovat/importovat větší množství záznamů a nestačí mu prostředky GUI nástrojů a ručně psaného SQL | |
| co to umí: | |
| - jednoduše vytvářet z dat v Excelu SQL dotazy typu INSERT, UPDATE, DELETE, INSERT IGNORE, REPLACE a INSERT ON DUPLICATE KEY UPDATE | |
| - ošetřuje vstupní hodnoty: | |
| - escapuje řetězce | |
| - formátuje čísla a datumy | |
| - rozpozná NULL (řetězec "NULL" nebo nenalezená hodnota #N/A) | |
| - dovede přijmout i literál (přímým zápisem řetězce do vzorce, např. "NOW()") | |
| - chytře generuje podmínky: | |
| - dosazuje správné operátory (=, LIKE, REGEXP, IN, IS NULL) | |
| - NULL testuje pomocí IS NULL ("IS NOT NULL" lze zapsat literálem) | |
| - u datumu konvertuje sloupec explicitně na DATE (sloupec může být DATETIME) | |
| - při zadání více hodnot než sloupců sloučí přebývající hodnoty pomocí operátoru IN | |
| - rozpoznává regulární výrazy (počáteční a koncové lomítko) a testuje je operátorem REGEXP | |
| - lze zadat i jednoduchý výraz - místo jména sloupce uvedete operátor (např. =Where("abc,*"; 10; 20) -> "`abc` = 10 * 20") | |
| - literály u podmínek se píší včetně operátoru (např. "= NOW()" nebo "IS NOT NULL") | |
| použití: | |
| =Insert(tabulka; sloupce; hodnoty...) | |
| =InsertIgnore(tabulka; sloupce; hodnoty...) | |
| =InsertReplace(tabulka; sloupce; hodnoty...) | |
| =InsertUpdate(tabulka; sloupce; hodnoty...) | |
| =Delete(tabulka; limit; sloupce podmínky; hodnoty podmínky...) | |
| =Update(tabulka; limit; Where(sloupce podmínky; hodnoty podmínky...); sloupce; hodnoty...) | |
| =Update2(tabulka; limit; sloupce podmínky; hodnoty podmínky; sloupce; hodnoty...) | |
| (`sloupce` je řetězec se seznamem sloupců odděleným čárkou) | |
| instalace: | |
| - v Excelu otevřete VBA editor (Alt+F11) | |
| - přidejte do projektu nový modul a zkopírujte do něj kód | |
| - funkce se dají použít jako běžné Excelové vzorce (musí být zapnutá makra) |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 'sada funkcí pro skládání SQL dotazů v Excelu | |
| 'autor: Vlasta Neubauer [@paranoiq], licence: public domain | |
| Function Insert(tbl, cols, val1, Optional val2, Optional val3, Optional val4, Optional val5, Optional val6, Optional val7, Optional val8, Optional val9, Optional val10, Optional val11, Optional val12, Optional val13, Optional val14, Optional val15, Optional val16, Optional val17, Optional val18, Optional val19, Optional val20) | |
| Dim sql As String | |
| sql = "INSERT INTO " & Table(tbl) & " (" & Columns(cols) & ") VALUES (" & Values(val1) | |
| If (Not IsMissing(val2)) Then sql = sql & ", " & Values(val2) | |
| If (Not IsMissing(val3)) Then sql = sql & ", " & Values(val3) | |
| If (Not IsMissing(val4)) Then sql = sql & ", " & Values(val4) | |
| If (Not IsMissing(val5)) Then sql = sql & ", " & Values(val5) | |
| If (Not IsMissing(val6)) Then sql = sql & ", " & Values(val6) | |
| If (Not IsMissing(val7)) Then sql = sql & ", " & Values(val7) | |
| If (Not IsMissing(val8)) Then sql = sql & ", " & Values(val8) | |
| If (Not IsMissing(val9)) Then sql = sql & ", " & Values(val9) | |
| If (Not IsMissing(val10)) Then sql = sql & ", " & Values(val10) | |
| If (Not IsMissing(val11)) Then sql = sql & ", " & Values(val11) | |
| If (Not IsMissing(val12)) Then sql = sql & ", " & Values(val12) | |
| If (Not IsMissing(val13)) Then sql = sql & ", " & Values(val13) | |
| If (Not IsMissing(val14)) Then sql = sql & ", " & Values(val14) | |
| If (Not IsMissing(val15)) Then sql = sql & ", " & Values(val15) | |
| If (Not IsMissing(val16)) Then sql = sql & ", " & Values(val16) | |
| If (Not IsMissing(val17)) Then sql = sql & ", " & Values(val17) | |
| If (Not IsMissing(val18)) Then sql = sql & ", " & Values(val18) | |
| If (Not IsMissing(val19)) Then sql = sql & ", " & Values(val19) | |
| If (Not IsMissing(val20)) Then sql = sql & ", " & Values(val20) | |
| Insert = sql & ");" | |
| End Function | |
| Function InsertIgnore(tbl, cols, val1, Optional val2, Optional val3, Optional val4, Optional val5, Optional val6, Optional val7, Optional val8, Optional val9, Optional val10, Optional val11, Optional val12, Optional val13, Optional val14, Optional val15, Optional val16, Optional val17, Optional val18, Optional val19, Optional val20) | |
| Dim sql As String | |
| sql = "INSERT IGNORE INTO " & Table(tbl) & " (" & Columns(cols) & ") VALUES (" & Values(val1) | |
| If (Not IsMissing(val2)) Then sql = sql & ", " & Values(val2) | |
| If (Not IsMissing(val3)) Then sql = sql & ", " & Values(val3) | |
| If (Not IsMissing(val4)) Then sql = sql & ", " & Values(val4) | |
| If (Not IsMissing(val5)) Then sql = sql & ", " & Values(val5) | |
| If (Not IsMissing(val6)) Then sql = sql & ", " & Values(val6) | |
| If (Not IsMissing(val7)) Then sql = sql & ", " & Values(val7) | |
| If (Not IsMissing(val8)) Then sql = sql & ", " & Values(val8) | |
| If (Not IsMissing(val9)) Then sql = sql & ", " & Values(val9) | |
| If (Not IsMissing(val10)) Then sql = sql & ", " & Values(val10) | |
| If (Not IsMissing(val11)) Then sql = sql & ", " & Values(val11) | |
| If (Not IsMissing(val12)) Then sql = sql & ", " & Values(val12) | |
| If (Not IsMissing(val13)) Then sql = sql & ", " & Values(val13) | |
| If (Not IsMissing(val14)) Then sql = sql & ", " & Values(val14) | |
| If (Not IsMissing(val15)) Then sql = sql & ", " & Values(val15) | |
| If (Not IsMissing(val16)) Then sql = sql & ", " & Values(val16) | |
| If (Not IsMissing(val17)) Then sql = sql & ", " & Values(val17) | |
| If (Not IsMissing(val18)) Then sql = sql & ", " & Values(val18) | |
| If (Not IsMissing(val19)) Then sql = sql & ", " & Values(val19) | |
| If (Not IsMissing(val20)) Then sql = sql & ", " & Values(val20) | |
| InsertIgnore = sql & ");" | |
| End Function | |
| Function InsertReplace(tbl, cols, val1, Optional val2, Optional val3, Optional val4, Optional val5, Optional val6, Optional val7, Optional val8, Optional val9, Optional val10, Optional val11, Optional val12, Optional val13, Optional val14, Optional val15, Optional val16, Optional val17, Optional val18, Optional val19, Optional val20) | |
| Dim sql As String | |
| sql = "REPLACE INTO " & Table(tbl) & " (" & Columns(cols) & ") VALUES (" & Values(val1) | |
| If (Not IsMissing(val2)) Then sql = sql & ", " & Values(val2) | |
| If (Not IsMissing(val3)) Then sql = sql & ", " & Values(val3) | |
| If (Not IsMissing(val4)) Then sql = sql & ", " & Values(val4) | |
| If (Not IsMissing(val5)) Then sql = sql & ", " & Values(val5) | |
| If (Not IsMissing(val6)) Then sql = sql & ", " & Values(val6) | |
| If (Not IsMissing(val7)) Then sql = sql & ", " & Values(val7) | |
| If (Not IsMissing(val8)) Then sql = sql & ", " & Values(val8) | |
| If (Not IsMissing(val9)) Then sql = sql & ", " & Values(val9) | |
| If (Not IsMissing(val10)) Then sql = sql & ", " & Values(val10) | |
| If (Not IsMissing(val11)) Then sql = sql & ", " & Values(val11) | |
| If (Not IsMissing(val12)) Then sql = sql & ", " & Values(val12) | |
| If (Not IsMissing(val13)) Then sql = sql & ", " & Values(val13) | |
| If (Not IsMissing(val14)) Then sql = sql & ", " & Values(val14) | |
| If (Not IsMissing(val15)) Then sql = sql & ", " & Values(val15) | |
| If (Not IsMissing(val16)) Then sql = sql & ", " & Values(val16) | |
| If (Not IsMissing(val17)) Then sql = sql & ", " & Values(val17) | |
| If (Not IsMissing(val18)) Then sql = sql & ", " & Values(val18) | |
| If (Not IsMissing(val19)) Then sql = sql & ", " & Values(val19) | |
| If (Not IsMissing(val20)) Then sql = sql & ", " & Values(val20) | |
| InsertReplace = sql & ");" | |
| End Function | |
| Function InsertUpdate(tbl, cols, val1, Optional val2, Optional val3, Optional val4, Optional val5, Optional val6, Optional val7, Optional val8, Optional val9, Optional val10, Optional val11, Optional val12, Optional val13, Optional val14, Optional val15, Optional val16, Optional val17, Optional val18, Optional val19, Optional val20) | |
| Dim vals As String | |
| Dim cvals As New Collection | |
| x = addValues(cvals, val1) | |
| If (Not IsMissing(val2)) Then x = addValues(cvals, val2) | |
| If (Not IsMissing(val3)) Then x = addValues(cvals, val3) | |
| If (Not IsMissing(val4)) Then x = addValues(cvals, val4) | |
| If (Not IsMissing(val5)) Then x = addValues(cvals, val5) | |
| If (Not IsMissing(val6)) Then x = addValues(cvals, val6) | |
| If (Not IsMissing(val7)) Then x = addValues(cvals, val7) | |
| If (Not IsMissing(val8)) Then x = addValues(cvals, val8) | |
| If (Not IsMissing(val9)) Then x = addValues(cvals, val9) | |
| If (Not IsMissing(val10)) Then x = addValues(cvals, val10) | |
| If (Not IsMissing(val11)) Then x = addValues(cvals, val11) | |
| If (Not IsMissing(val12)) Then x = addValues(cvals, val12) | |
| If (Not IsMissing(val13)) Then x = addValues(cvals, val13) | |
| If (Not IsMissing(val14)) Then x = addValues(cvals, val14) | |
| If (Not IsMissing(val15)) Then x = addValues(cvals, val15) | |
| If (Not IsMissing(val16)) Then x = addValues(cvals, val16) | |
| If (Not IsMissing(val17)) Then x = addValues(cvals, val17) | |
| If (Not IsMissing(val18)) Then x = addValues(cvals, val18) | |
| If (Not IsMissing(val19)) Then x = addValues(cvals, val19) | |
| If (Not IsMissing(val20)) Then x = addValues(cvals, val20) | |
| vals = "" | |
| For Each valx In cvals | |
| vals = vals & valx & ", " | |
| Next | |
| vals = Left(vals, Len(vals) - 2) | |
| InsertUpdate = "INSERT INTO " & Table(tbl) & " (" & Columns(cols) & ") VALUES (" & vals & ") ON DUPLICATE KEY UPDATE " & Pairs(cols, cvals) & ";" | |
| End Function | |
| Function Delete(tbl, limit, cols, val1, Optional val2, Optional val3, Optional val4, Optional val5, Optional val6, Optional val7, Optional val8, Optional val9, Optional val10, Optional val11, Optional val12, Optional val13, Optional val14, Optional val15, Optional val16, Optional val17, Optional val18, Optional val19, Optional val20) | |
| Dim sql As String | |
| Dim cvals As New Collection | |
| x = addValues(cvals, val1) | |
| If (Not IsMissing(val2)) Then x = addValues(cvals, val2) | |
| If (Not IsMissing(val3)) Then x = addValues(cvals, val3) | |
| If (Not IsMissing(val4)) Then x = addValues(cvals, val4) | |
| If (Not IsMissing(val5)) Then x = addValues(cvals, val5) | |
| If (Not IsMissing(val6)) Then x = addValues(cvals, val6) | |
| If (Not IsMissing(val7)) Then x = addValues(cvals, val7) | |
| If (Not IsMissing(val8)) Then x = addValues(cvals, val8) | |
| If (Not IsMissing(val9)) Then x = addValues(cvals, val9) | |
| If (Not IsMissing(val10)) Then x = addValues(cvals, val10) | |
| If (Not IsMissing(val11)) Then x = addValues(cvals, val11) | |
| If (Not IsMissing(val12)) Then x = addValues(cvals, val12) | |
| If (Not IsMissing(val13)) Then x = addValues(cvals, val13) | |
| If (Not IsMissing(val14)) Then x = addValues(cvals, val14) | |
| If (Not IsMissing(val15)) Then x = addValues(cvals, val15) | |
| If (Not IsMissing(val16)) Then x = addValues(cvals, val16) | |
| If (Not IsMissing(val17)) Then x = addValues(cvals, val17) | |
| If (Not IsMissing(val18)) Then x = addValues(cvals, val18) | |
| If (Not IsMissing(val19)) Then x = addValues(cvals, val19) | |
| If (Not IsMissing(val20)) Then x = addValues(cvals, val20) | |
| sql = "DELETE FROM " & Table(tbl) & " WHERE " & Where(cols, cvals) | |
| If (limit) Then sql = sql & " LIMIT " & limit | |
| Delete = sql & ";" | |
| End Function | |
| Function Update(tbl, limit, condition, cols, val1, Optional val2, Optional val3, Optional val4, Optional val5, Optional val6, Optional val7, Optional val8, Optional val9, Optional val10, Optional val11, Optional val12, Optional val13, Optional val14, Optional val15, Optional val16, Optional val17, Optional val18, Optional val19, Optional val20) | |
| Dim sql As String | |
| Dim cvals As New Collection | |
| x = addValues(cvals, val1) | |
| If (Not IsMissing(val2)) Then x = addValues(cvals, val2) | |
| If (Not IsMissing(val3)) Then x = addValues(cvals, val3) | |
| If (Not IsMissing(val4)) Then x = addValues(cvals, val4) | |
| If (Not IsMissing(val5)) Then x = addValues(cvals, val5) | |
| If (Not IsMissing(val6)) Then x = addValues(cvals, val6) | |
| If (Not IsMissing(val7)) Then x = addValues(cvals, val7) | |
| If (Not IsMissing(val8)) Then x = addValues(cvals, val8) | |
| If (Not IsMissing(val9)) Then x = addValues(cvals, val9) | |
| If (Not IsMissing(val10)) Then x = addValues(cvals, val10) | |
| If (Not IsMissing(val11)) Then x = addValues(cvals, val11) | |
| If (Not IsMissing(val12)) Then x = addValues(cvals, val12) | |
| If (Not IsMissing(val13)) Then x = addValues(cvals, val13) | |
| If (Not IsMissing(val14)) Then x = addValues(cvals, val14) | |
| If (Not IsMissing(val15)) Then x = addValues(cvals, val15) | |
| If (Not IsMissing(val16)) Then x = addValues(cvals, val16) | |
| If (Not IsMissing(val17)) Then x = addValues(cvals, val17) | |
| If (Not IsMissing(val18)) Then x = addValues(cvals, val18) | |
| If (Not IsMissing(val19)) Then x = addValues(cvals, val19) | |
| If (Not IsMissing(val20)) Then x = addValues(cvals, val20) | |
| sql = "UPDATE " & Table(tbl) & " SET " & Pairs(cols, cvals) & " WHERE " & condition | |
| If (limit) Then sql = sql & " LIMIT " & limit | |
| Update = sql & ";" | |
| End Function | |
| Function Update2(tbl, limit, cond_cols, cond_vals, cols, val1, Optional val2, Optional val3, Optional val4, Optional val5, Optional val6, Optional val7, Optional val8, Optional val9, Optional val10, Optional val11, Optional val12, Optional val13, Optional val14, Optional val15, Optional val16, Optional val17, Optional val18, Optional val19, Optional val20) | |
| Dim sql As String | |
| Dim cvals As New Collection | |
| x = addValues(cvals, val1) | |
| If (Not IsMissing(val2)) Then x = addValues(cvals, val2) | |
| If (Not IsMissing(val3)) Then x = addValues(cvals, val3) | |
| If (Not IsMissing(val4)) Then x = addValues(cvals, val4) | |
| If (Not IsMissing(val5)) Then x = addValues(cvals, val5) | |
| If (Not IsMissing(val6)) Then x = addValues(cvals, val6) | |
| If (Not IsMissing(val7)) Then x = addValues(cvals, val7) | |
| If (Not IsMissing(val8)) Then x = addValues(cvals, val8) | |
| If (Not IsMissing(val9)) Then x = addValues(cvals, val9) | |
| If (Not IsMissing(val10)) Then x = addValues(cvals, val10) | |
| If (Not IsMissing(val11)) Then x = addValues(cvals, val11) | |
| If (Not IsMissing(val12)) Then x = addValues(cvals, val12) | |
| If (Not IsMissing(val13)) Then x = addValues(cvals, val13) | |
| If (Not IsMissing(val14)) Then x = addValues(cvals, val14) | |
| If (Not IsMissing(val15)) Then x = addValues(cvals, val15) | |
| If (Not IsMissing(val16)) Then x = addValues(cvals, val16) | |
| If (Not IsMissing(val17)) Then x = addValues(cvals, val17) | |
| If (Not IsMissing(val18)) Then x = addValues(cvals, val18) | |
| If (Not IsMissing(val19)) Then x = addValues(cvals, val19) | |
| If (Not IsMissing(val20)) Then x = addValues(cvals, val20) | |
| sql = "UPDATE " & Table(tbl) & " SET " & Pairs(cols, cvals) & " WHERE " & Where(cond_cols, cond_vals) | |
| If (limit) Then sql = sql & " LIMIT " & limit | |
| Update2 = sql & ";" | |
| End Function | |
| Function Table(tbl) | |
| Dim sql As String | |
| Dim parts() As String | |
| If (InStr(1, tbl, ".")) Then | |
| parts = Split(Replace(tbl, " ", ""), ".") | |
| For n = 0 To UBound(parts) | |
| sql = sql & "`" & (parts(n)) & "`." | |
| Next | |
| Table = Left(sql, Len(sql) - 1) | |
| Else | |
| Table = "`" & tbl & "`" | |
| End If | |
| End Function | |
| Function Where(cols, val1, Optional val2, Optional val3, Optional val4, Optional val5, Optional val6, Optional val7, Optional val8, Optional val9, Optional val10, Optional val11, Optional val12, Optional val13, Optional val14, Optional val15, Optional val16, Optional val17, Optional val18, Optional val19, Optional val20) | |
| Dim sql As String | |
| Dim acols() As String | |
| Dim ccols As New Collection | |
| Dim cvals As New Collection | |
| Dim regex As Object | |
| Set regex = CreateObject("VBScript.RegExp") | |
| regex.Pattern = "^'\d{4}-\d{2}-\d{2}'$" | |
| If (TypeOf cols Is Range) Then | |
| For Each cell In cols | |
| If (cell <> "") Then ccols.Add (Column(cell)) | |
| Next | |
| Else | |
| acols = Split(Replace(cols, " ", ""), ",") | |
| For n = 0 To UBound(acols) | |
| ccols.Add (Column(acols(n))) | |
| Next | |
| End If | |
| If (TypeOf val1 Is Collection) Then | |
| For n = 1 To val1.Count | |
| cvals.Add (val1(n)) | |
| Next | |
| Else | |
| x = addValues(cvals, val1) | |
| If (Not IsMissing(val2)) Then x = addValues(cvals, val2) | |
| If (Not IsMissing(val3)) Then x = addValues(cvals, val3) | |
| If (Not IsMissing(val4)) Then x = addValues(cvals, val4) | |
| If (Not IsMissing(val5)) Then x = addValues(cvals, val5) | |
| If (Not IsMissing(val6)) Then x = addValues(cvals, val6) | |
| If (Not IsMissing(val7)) Then x = addValues(cvals, val7) | |
| If (Not IsMissing(val8)) Then x = addValues(cvals, val8) | |
| If (Not IsMissing(val9)) Then x = addValues(cvals, val9) | |
| If (Not IsMissing(val10)) Then x = addValues(cvals, val10) | |
| If (Not IsMissing(val11)) Then x = addValues(cvals, val11) | |
| If (Not IsMissing(val12)) Then x = addValues(cvals, val12) | |
| If (Not IsMissing(val13)) Then x = addValues(cvals, val13) | |
| If (Not IsMissing(val14)) Then x = addValues(cvals, val14) | |
| If (Not IsMissing(val15)) Then x = addValues(cvals, val15) | |
| If (Not IsMissing(val16)) Then x = addValues(cvals, val16) | |
| If (Not IsMissing(val17)) Then x = addValues(cvals, val17) | |
| If (Not IsMissing(val18)) Then x = addValues(cvals, val18) | |
| If (Not IsMissing(val19)) Then x = addValues(cvals, val19) | |
| If (Not IsMissing(val20)) Then x = addValues(cvals, val20) | |
| End If | |
| If (ccols.Count < cvals.Count) Then | |
| skipLast = 1 | |
| Else | |
| skipLast = 0 | |
| End If | |
| If (Not skipLast Or ccols.Count > 1) Then | |
| For n = 1 To ccols.Count - skipLast | |
| If (Left(ccols(n), 1) = "`") Then | |
| If (cvals(n) = "NULL") Then | |
| sql = sql & ccols(n) & " IS NULL AND " | |
| ElseIf (IsNumeric(cvals(n))) Then | |
| sql = sql & ccols(n) & " = " & cvals(n) & " AND " | |
| ElseIf (regex.Test(cvals(n))) Then | |
| sql = sql & "DATE(" & ccols(n) & ") = " & cvals(n) & " AND " | |
| ElseIf (Left(cvals(n), 2) = "'/" And Right(cvals(n), 2) = "/'") Then | |
| sql = sql & ccols(n) & " REGEXP '" & Mid(cvals(n), 3, Len(cvals(n)) - 4) & "' AND " | |
| ElseIf (Left(cvals(n), 1) = "'") Then | |
| sql = sql & ccols(n) & " LIKE " & cvals(n) & " AND " | |
| Else | |
| sql = sql & ccols(n) & " " & cvals(n) & " AND " | |
| End If | |
| Else | |
| sql = Left(sql, Len(sql) - 5) & " " & ccols(n) & " " & cvals(n) & " AND " | |
| End If | |
| Next | |
| End If | |
| If (skipLast) Then | |
| If ((regex.Test(cvals(ccols.Count)))) Then | |
| sql = sql & "DATE(" & ccols(ccols.Count) & ") IN (" | |
| Else | |
| sql = sql & ccols(ccols.Count) & " IN (" | |
| End If | |
| For n = 0 To cvals.Count - ccols.Count | |
| sql = sql & cvals(ccols.Count + n) & ", " | |
| Next | |
| sql = Left(sql, Len(sql) - 2) & ")" | |
| Else | |
| sql = Left(sql, Len(sql) - 5) | |
| End If | |
| Where = sql | |
| End Function | |
| Function Pairs(cols, val1, Optional val2, Optional val3, Optional val4, Optional val5, Optional val6, Optional val7, Optional val8, Optional val9, Optional val10, Optional val11, Optional val12, Optional val13, Optional val14, Optional val15, Optional val16, Optional val17, Optional val18, Optional val19, Optional val20) | |
| Dim sql As String | |
| Dim acols() As String | |
| Dim ccols As New Collection | |
| Dim cvals As New Collection | |
| If (TypeOf cols Is Range) Then | |
| For Each cell In cols | |
| If (cell <> "") Then ccols.Add (Column(cell)) | |
| Next | |
| Else | |
| acols = Split(Replace(cols, " ", ""), ",") | |
| For n = 0 To UBound(acols) | |
| ccols.Add (Column(acols(n))) | |
| Next | |
| End If | |
| If (TypeOf val1 Is Collection) Then | |
| For n = 1 To val1.Count | |
| cvals.Add (val1(n)) | |
| Next | |
| Else | |
| x = addValues(cvals, val1) | |
| If (Not IsMissing(val2)) Then x = addValues(cvals, val2) | |
| If (Not IsMissing(val3)) Then x = addValues(cvals, val3) | |
| If (Not IsMissing(val4)) Then x = addValues(cvals, val4) | |
| If (Not IsMissing(val5)) Then x = addValues(cvals, val5) | |
| If (Not IsMissing(val6)) Then x = addValues(cvals, val6) | |
| If (Not IsMissing(val7)) Then x = addValues(cvals, val7) | |
| If (Not IsMissing(val8)) Then x = addValues(cvals, val8) | |
| If (Not IsMissing(val9)) Then x = addValues(cvals, val9) | |
| If (Not IsMissing(val10)) Then x = addValues(cvals, val10) | |
| If (Not IsMissing(val11)) Then x = addValues(cvals, val11) | |
| If (Not IsMissing(val12)) Then x = addValues(cvals, val12) | |
| If (Not IsMissing(val13)) Then x = addValues(cvals, val13) | |
| If (Not IsMissing(val14)) Then x = addValues(cvals, val14) | |
| If (Not IsMissing(val15)) Then x = addValues(cvals, val15) | |
| If (Not IsMissing(val16)) Then x = addValues(cvals, val16) | |
| If (Not IsMissing(val17)) Then x = addValues(cvals, val17) | |
| If (Not IsMissing(val18)) Then x = addValues(cvals, val18) | |
| If (Not IsMissing(val19)) Then x = addValues(cvals, val19) | |
| If (Not IsMissing(val20)) Then x = addValues(cvals, val20) | |
| End If | |
| For n = 1 To ccols.Count | |
| If (Left(ccols(n), 1) = "`") Then | |
| sql = sql & ccols(n) & " = " & cvals(n) & ", " | |
| Else | |
| sql = Left(sql, Len(sql) - 2) & " " & ccols(n) & " " & cvals(n) & ", " | |
| End If | |
| Next | |
| sql = Left(sql, Len(sql) - 2) | |
| Pairs = sql | |
| End Function | |
| Function Columns(cols) | |
| Dim sql As String | |
| Dim acols() As String | |
| If (TypeOf cols Is Range) Then | |
| For Each cell In cols | |
| sql = sql & Column(cell) & ", " | |
| Next | |
| sql = Left(sql, Len(sql) - 2) | |
| Else | |
| acols = Split(Replace(cols, " ", ""), ",") | |
| For n = 0 To UBound(acols) | |
| sql = sql & Column(acols(n)) & ", " | |
| Next | |
| sql = Left(sql, Len(sql) - 2) | |
| End If | |
| Columns = sql | |
| End Function | |
| Function Column(col) | |
| Dim regex As Object | |
| Set regex = CreateObject("VBScript.RegExp") | |
| regex.Pattern = "^[a-zA-Z0-9_]+$" | |
| If (regex.Test(col)) Then | |
| Column = "`" & col & "`" | |
| Else | |
| Column = col | |
| End If | |
| End Function | |
| Function Values(vals) | |
| Dim sql As String | |
| If (TypeOf vals Is Range) Then | |
| For Each cell In vals | |
| sql = sql & Value(cell) & ", " | |
| Next | |
| sql = Left(sql, Len(sql) - 2) | |
| Else | |
| sql = Value(vals) | |
| End If | |
| Values = sql | |
| End Function | |
| Function Value(val, Optional escape = 0) | |
| If (IsError(val)) Then | |
| Value = "NULL" | |
| ElseIf (IsNull(val) Or val = "NULL") Then | |
| Value = "NULL" | |
| ElseIf (IsEmpty(val) Or val = "") Then | |
| Value = "''" | |
| ElseIf (IsNumeric(val)) Then | |
| Value = Replace(Replace(val, ",", "."), " ", "") | |
| ElseIf (TypeOf val Is Range) Then | |
| If (InStr(1, val.NumberFormatLocal, "h")) Then | |
| Value = "'" & Format(val.Value2, "yyyy-mm-dd hh:mm:ss") & "'" | |
| ElseIf (InStr(1, val.NumberFormatLocal, "d")) Then | |
| Value = "'" & Format(val.Value2, "yyyy-mm-dd") & "'" | |
| Else | |
| Value = "'" & Replace(Replace(val, "\", "\\"), "'", "\'") & "'" | |
| End If | |
| ElseIf (escape Or (Left(val, 1) = "/" And Right(val, 1) = "/")) Then | |
| Value = "'" & Replace(Replace(val, "\", "\\"), "'", "\'") & "'" | |
| Else | |
| Value = val | |
| End If | |
| End Function | |
| Private Function addValues(avals, vals) | |
| If (TypeOf vals Is Range) Then | |
| For Each cell In vals | |
| avals.Add (Value(cell)) | |
| Next | |
| Else | |
| avals.Add (Value(vals)) | |
| End If | |
| End Function |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment