Public driver As New Selenium.ChromeDriver
Sub update_database()
On Error GoTo errorhandler
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
Dim strFile As String
Dim blnContinue As Boolean
Dim dteStart As Date
Dim ws As Worksheet
Const lngWait As Long = 15
'1. OPEN CHROME AND INITIALIZE THE DOWNLOAD.
With driver
Call .Start("chrome")
SendKeys ("%{TAB}")
SendKeys "{NUMLOCK}"
Call .Get("https://onedrive.live.com/download?cid=xxxxxxxxxxxxxxxx&resid=xxxxxxxxxxxxxxxxxxxxxx&authkey=xxxxxxxxxxxxxxx&em=2")
End With
'2. TRY AND IDENTIFY THE DOWNLOADED FILE.
strFile = Environ$("USERPROFILE") & "\Downloads\DATA ENTRY.xlsm"
dteStart = Now 'log start time
Do
DoEvents
blnContinue = CBool(Len(Dir(strFile)))
Loop Until blnContinue Or Now > dteStart + TimeSerial(0, 0, lngWait)
'3. COPY THE CONTENTS OF THE DOWNLOADED FILE IF IT EXISTS AND CLOSE CHROME. WARN THE USER IF FILE DID NOT DOWNLOAD.
If blnContinue Then
Call driver.Quit
Workbooks.Open Filename:=strFile
Set WSArray = Workbooks("REPORTS.xlsm").Worksheets(Array("INDEX", "DATABASE", "R VALIDATION", "R DAILY PRODUCTION", "R FRI DPI LABTEST", "R PO DESIGN COLOUR"))
Set WSArray1 = Workbooks("REPORTS.xlsm").Worksheets(Array("DATABASE", "R VALIDATION", "R DAILY PRODUCTION", "R FRI DPI LABTEST", "R PO DESIGN COLOUR"))
For Each ws In WSArray
ws.Unprotect Password:="merchant"
Next
For Each ws In WSArray1
ws.Visible = True
Next
'''''''''''''''''''''''''''''''''''''''''''
Workbooks("REPORTS.xlsm").Worksheets("R VALIDATION").Cells.ClearContents
Workbooks("DATA ENTRY.xlsm").Worksheets("VALIDATION").Cells.Copy
Workbooks("REPORTS.xlsm").Worksheets("R VALIDATION").Range("A1").PasteSpecial Paste:=xlPasteValues
'''''''''''''''''''''''''''''''''''''''''''
Workbooks("REPORTS.xlsm").Worksheets("DATABASE").Range("A4:AO1002").ClearContents
If Workbooks("DATA ENTRY.xlsm").Worksheets("ORDERS").AutoFilterMode = True _
Then Workbooks("DATA ENTRY.xlsm").Worksheets("ORDERS").AutoFilterMode = False
Workbooks("DATA ENTRY.XLSM").Worksheets("ORDERS").Range("A2:AO10000").Copy
Workbooks("REPORTS.xlsm").Worksheets("DATABASE").Range("A4").PasteSpecial Paste:=xlPasteValues
Range("A3").Select
'''''''''''''''''''''''''''''''''''''''''''
Workbooks("REPORTS.xlsm").Worksheets("R DAILY PRODUCTION").Cells.ClearContents
Workbooks("DATA ENTRY.xlsm").Worksheets("DAILY PRODUCTION").Cells.Copy
Workbooks("REPORTS.xlsm").Worksheets("R DAILY PRODUCTION").Range("A1").PasteSpecial Paste:=xlPasteValues
'''''''''''''''''''''''''''''''''''''''''''
Workbooks("REPORTS.xlsm").Worksheets("R FRI DPI LABTEST").Cells.ClearContents
Workbooks("DATA ENTRY.xlsm").Worksheets("FRI DPI LABTEST").Cells.Copy
Workbooks("REPORTS.xlsm").Worksheets("R FRI DPI LABTEST").Range("A1").PasteSpecial Paste:=xlPasteValues
'''''''''''''''''''''''''''''''''''''''''''
Workbooks("REPORTS.xlsm").Worksheets("R PO DESIGN COLOUR").Cells.ClearContents
Workbooks("DATA ENTRY.xlsm").Worksheets("PO DESIGN COLOUR").Cells.Copy
Workbooks("REPORTS.xlsm").Worksheets("R PO DESIGN COLOUR").Range("A1").PasteSpecial Paste:=xlPasteValues
'''''''''''''''''''''''''''''''''''''''''''
Workbooks("DATA ENTRY.xlsm").Close SaveChanges:=False
kill Environ$("USERPROFILE") & "\Downloads\DATA ENTRY" & "*"""
Workbooks("REPORTS.xlsm").Save
'''''''''''''''''''''''''''''''''''''''''''
For Each ws In WSArray1
ws.Visible = xlSheetVeryHidden
Next
Sheets("INDEX").Range("A41").Value = Now
For Each ws In WSArray
ws.Protect Password:="merchant", DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingColumns:=True, AllowFormattingRows:=True
Next
Else
Call driver.Quit
kill Environ$("USERPROFILE") & "\Downloads\DATA ENTRY" & "*"""
Call msg
End If
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Exit Sub
errorhandler:
Call driver.Quit
Call msg
End Sub