Export data into XLS format

MM_GR_STRING = "ConnectionString"


Dim Recordset1
Dim Recordset1_numRows
Dim EmailBody

Set Recordset1 = CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_GR_STRING

strSQL = "SELECT * FROM viewWeeklyrdersReport ORDER BY DateProcessed"             
               
Recordset1.Source = strSQL
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()
Recordset1_numRows = 0

VarDate = Date
VarLen = len(VarDate)
If VarLen = 10 then 'xx-xx-xxxx
Var1 = Left(VarDate , 2)
Var3 = Right(VarDate , 4)
Var2 = Mid(VarDate , 4, 2)

elseif VarLen = 9 AND Mid(VarDate , 2, 1) = "/" then 'x-xx-xxxx
Var1 = Left(VarDate , 1)
Var3 = Right(VarDate , 4)
Var2 = Mid(VarDate , 3, 2)
Var1 = "0"&Var1

elseif VarLen = 9 AND Mid(VarDate , 3, 1) = "/" then 'xx-x-xxxx
Var1 = Left(VarDate , 2)
Var3 = Right(VarDate , 4)
Var2 = Mid(VarDate , 4, 1)
Var2 = "0"&Var2

else 'x-x-xxxx
Var1 = Left(VarDate , 1)
Var3 = Right(VarDate , 4)
Var2 = Mid(VarDate , 3, 1)
Var1 = "0"&Var1
Var2 = "0"&Var2
end if

VarDate = Var2&Var1&Var3
VarQ = """"

set objComm = CreateObject("ADODB.Command")
objComm.ActiveConnection = MM_GR_STRING

set FSO = CreateObject("scripting.FileSystemObject") 'text file
GoodsFileName = "Goods_045799_" & Right("0" & Day(Now), 2) & Right("0" & Month(Now), 2) & Year(Now)
OutputDir = "D:\WEB\WeeklyWebReports\"
set myFile = fso.CreateTextFile(OutputDir & GoodsFileName & ".csv", true)  
myFile.Write("""Invoice Type"",""Supplier Name"",""Supplier No"",""SAP Supplier No"",""Address Line 1"",""Address Line 2"",""Address Line 3"",""City/County"",""Postcode"",""Country"",""Y order number"",""Item Number"",""Quantity"",""Net Cost (EX VAT)"",""VAT"",""Invoice Number Goods"",""Invoice Date""")
 
While Not Recordset1.EOF
       
        ' Web Orders CSV ##################################################################
	'IF Cint(Recordset1.Fields.Item("DropShipQty").Value) > 1 Then
	'Total = Cint(Recordset1.Fields.Item("DropShipQty").Value) * Cdbl(Recordset1.Fields.Item("NetPrice").Value)
	'Else
	Total = Recordset1.Fields.Item("NetPrice").Value
        'End IF
        myFile.Write VbCrLf 
        myFile.Write "Goods," 'Invoice Type
        myFile.Write "1232," 'OrderID    
        myFile.Write "045799," 'Supplier No
        myFile.Write "1000026617," 'SAP Supplier No
        myFile.Write "4 Road," 'Address Line 1
	myFile.Write "test," 'Address Line 2
	myFile.Write "test," 'Address Line 3
        myFile.Write "London," 'City/County        
        myFile.Write "ES14gs," 'Postcode
        myFile.Write "UK," 'Country       
        myFile.Write """" & Trim((Recordset1.Fields.Item("DropShipRetailerOrderID").Value)) & """," 'Y order number
        myFile.Write """" & Trim((Recordset1.Fields.Item("ProdID").Value)) & """," 'Item Number       
        myFile.Write "1," 'Quantity
        myFile.Write """" & Trim(Total) & """," 'Net Cost (EX VAT)      
        myFile.Write """" & Trim((Recordset1.Fields.Item("NetVAT").Value)) & """," 'VAT        
        myFile.Write "," 'Invoice Number Goods       
        myFile.Write "," 'Invoice Date       
        Recordset1.MoveNext
Wend
 
myFile.Close 'close the text files

Select allOpen in new window