[VBA] Extracting Data from SQL with a Button in Excel

[Requirements]

Business transport team asks a Excel sheet to fetch all purchase order data in ERP system

- Create Excel file with VBA embedded which fetchs data

- Create SQL procedure calling ERP data from SQL database

 

[OS] 

Windows 10

 

[Development Language]

VBA, SQL

 

[IDE]

SSMS(SQL Server Management Studio), Microsoft Visual Basic For Applications

 

[Code]

VBA script: GetData_from_SQLServerDatabase_with_VBA_Excel()

Option Explicit

Public Sub GetData_from_SQLServerDatabase_with_VBA_Excel()
    'Variables
    Dim conn As Object: Set conn = CreateObject("ADODB.Connection") 'connection
    Dim rst As Object: Set rst = CreateObject("ADODB.Recordset")    'record set
    Dim iCols As Integer                                            'counting
    Dim SQry As String                                              'SQL query
    
       
    'Setting up sql connection
    conn.ConnectionString = [connection string]
    SQry = "exec Custom_SP_SC_PrintPOData"
    conn.ConnectionTimeout = 30
    
    'Conneting SQL data
    conn.Open
    
    'Getting data and save it to record set
    rst.Open SQry, conn
    
    'Clearing data in the sheet before printing out data
    Sheets("SAP Data").Cells.Clear
    Sheets("SAP Data").Activate
    
    
    For iCols = 0 To rst.Fields.Count - 1
        Worksheets("SAP Data").Cells(1, iCols + 1).Value = rst.Fields(iCols).Name
    Next
    
    Worksheets("SAP Data").Range("A2").CopyFromRecordset rst
    
    rst.Close
    conn.Close
    
    
    
End Sub

 

 

SQL procedure: Custom_SP_SC_PrintPOData 

USE [YTC_REAL]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Alter PROCEDURE [dbo].[Custom_SP_SC_PrintPOData]
AS

BEGIN

	select 
		poh.DocNum as 'P/O Number'
		,pol.DocDate as 'Creation Date'
		,case 
			when pol.LineStatus = 'O' then 'OPEN' 
			when pol.LineStatus = 'C' then 'CLOSE' 
			else '' 
			End as 'P/O Status'
		--,pol.LineStatus as 'P/O Status'
		,pol.LineNum as 'Line'
		,pol.ItemCode as 'Part'
		,pol.Dscription as 'Part Description'
		,pol.Quantity as 'Order Qty'
		,pol.U_stockqty as 'Order Qty Received'
		,'TBD' as 'Ex-Works Date'
		,'TBD' as 'Need in Plant / MRP Date'
		,'EA' as 'Unit Value'
		,pol.Currency as 'Currency Code'
		,pol.Rate as 'Exchange Rate'
		,case 
			when icts.U_IncoTerm = 2 then 'FOB'
			when icts.U_IncoTerm = 4 then 'CIF'
			when icts.U_IncoTerm = 5 then 'DDU'
			when icts.U_IncoTerm = 6 then 'CFR'
			when icts.U_IncoTerm = 7 then 'DDP'
			when icts.U_IncoTerm = 8 then 'EX Work'
			else '' 
			End as 'Terms of sale / IncoTerms'
		,'TBD' as 'Export HS Code'
		,'TBD' as 'Commodity Code (Import HS code)'
		,poh.cardCode as 'Supplier Number'
		,poh.cardName as 'Supplier Name'
		,crd1.address as 'Supplier Address 1'
		,crd1.address2 as 'Supplier Address 2'
		,crd1.address3 as 'Supplier Address 3'
		,crd1.ZipCode as 'Supplier Post Code / ZIP'
		,crd1.Country as 'Supplier Country Code'
		,'Rotork YTC' as 'Delivery Name'
		,'81 Hwanggeum-ro, 89 beon-gil, Gimpo-si, Gyeonggi-do' as 'Delivery Address 1'
		,'10048' as 'Delivery Post Code'
		,'KR' as 'Delivery Country'

	from por1 pol
	inner join opor poh on poh.DocNum = pol.U_ponum
	inner join ocrd crd on crd.CardCode = poh.CardCode
	inner join CRD1 crd1 on crd1.CardCode = poh.CardCode and crd1.AdresType = 'B'
	inner join [@DBS_OPOR] icts on icts.U_DocEntry = poh.DocEntry
	where poh.DocNum = '47347'

END

 

[Test]

- Open Excel file and click "Extracting SAP Purchase Order Data" button

** To run the VBA script, Trust Center configuration required

1. Enable VBA macros

2. Add trusted location where the Excel file is saved

 

- Works quite well

 

[Lesson Learned]

- How to find fiels in SAP customized table

Select * From CUFD
Where aliasID = [field name]

 

- SQL "Case" statement

Case 

   When [table.field] 'ABC' then 'DEF'

   ....

   Else ''

End as [Column Name]