Using VBA to upload Excel table to SQL table
Hi all,
I have been trying to upload an excel table with a fixed ranged to an existing SQL but keep getting an error message (I am 64 bit OS):
Option Explicit
Public Declare PtrSafe Sub GetSystemTime Lib "Kernel32" (ByRef lpSystemTime As U)
Public Const strConn = "DSN=fcfinance"
Public strLoadTable As String
Sub One_Sub_To_Rule_Them_All()
'This just calls the below macros in order.
Call SpeedDaemonOn
Application.StatusBar = "Loading Data to SQL..."
Load_Data_to_SQL_1
'Load_Data_to_SQL_2 '>>>This is for if you need multiple sheets/tables uploaded (just copy the sub and put the new range/table refs
'Sheets("Control").Range("A2").Value = Format(Now(), "yyyy-MM-dd hh:mm:ss") '>>>This is if you want to have a "Last Upload" timestamp saved anywhere.
Application.StatusBar = "Ready"
Call SpeedDaemonOff
'MsgBox "Data loaded to SQL" ' Optional confirmation message for the user, currently commented out.
End Sub
Sub SpeedDaemonO()
'Turns off screen updating and auto calculation to make things run faster
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
End Sub
Sub SpeedDaemonOff()
'Turns oscreen updating and auto calculation back on
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub
Sub Load_Data_to_SQL_1()
'In order for this to work properly, a reference to ADO must be set. In VBA, select Tools->
'References and check the box next to 'Microsoft ActiveX Data Objects 2.8 Library' (version number may be different).
'If you get an error message saying that this conflicts with something you already have active, just leave it off.
Dim strSQL As String
Dim strSaveFile As String
Dim tmp_strSaveFile As String
strSaveFile = ThisWorkbook.Path & "\tmp_SQL_load_VCM_" & Environ("username") & "_" & Format(Now(), "yyyy-MM-dd_hh-mm-ss") & ".txt"
'>>> By default, the temp files generated by this workbook will live in the same source directory as the workbook itself. for example, if the user is running a file from their desktop, the temporary text files will be saved there.
strLoadTable = "fcfinance_sandbox.DAT_BOTTOMS_UP" '>>>Change this to your destination table. MAKE SURE that your columns/formats match what's in Excel. Avoid comma formatting for numbers (ex. 1,000,000 - can be finickiy)
'save temporary text file with load data
Sheets("Upload").Range("A1:M41601").Copy '>>> This is your destination data range. Recommend leaving it on column references to allow for dynamic number of rows. See notes below:
'>>> Any Dates MUST be formatted as dates/text in "YYYY-MM-DD" format (available under custom formats in excel)
'>>> There are several ways to do this, including a dynamic named range, but this has been the easiest and worked well for me.
Workbooks.Add
Cells(1, 1).PasteSpecial
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs strSaveFile, FileFormat:=xlText
ActiveWorkbook.Close (False)
Application.DisplayAlerts = True
'SQL for loading (replacing backslashes for forward slashes to load to MySQL). No need to change any of this code (variables are declared above for dest table and source range).
strSQL = "LOAD DATA LOCAL INFILE '" & Replace(strSaveFile, "\", "/") & "' REPLACE INTO TABLE " & strLoadTable & " FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '""' LINES TERMINATED BY '\r\n' IGNORE 1 LINES"
Dim AdoConnect As ADODB.Connection
Set AdoConnect = New ADODB.Connection
AdoConnect.Open strConn 'using fcfinance DSN as declared in public section. Will error out if the user has named their ODBC connection something else (like Fcfinance, fcfinance-ro, etc.)
Dim QueryExecute As ADODB.Command
Set QueryExecute = New ADODB.Command
QueryExecute.ActiveConnection = AdoConnect
QueryExecute.CommandText = strSQL 'execute the sql statement (variable strSQL) to insert data.
QueryExecute.Execute
AdoConnect.Close
Kill strSaveFile 'closes the temp workbook
End Sub
___________________ ___________________ ___________________
The error message I get is 'Compile error: user-defined type not defined' and it applies to this line of code: Public Declare PtrSafe Sub GetSystemTime Lib "Kernel32" (ByRef lpSystemTime As U)