MySQL Forums
Forum List  »  MySQL for Excel

Using VBA to upload Excel table to SQL table
Posted by: Andrew DiMartino
Date: July 02, 2020 09:17AM

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)

Options: ReplyQuote


Subject
Views
Written By
Posted
Using VBA to upload Excel table to SQL table
1668
July 02, 2020 09:17AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.