Re: Covid and Excel VBA

I have in my time learned two or three different types of computer coding, from the very early HTML and progressed to others used in the printing industry. All computer coding seems complicated to me, probably easier as it's used regularly and can be 'read' more easily.

However, out of curiosity I checked out VBA in Microsoft Excel to find out just what it is:

https://www.excel-easy.com/vba.html

Re: Covid and Excel VBA

Visual Basic for Applications is a scripting language based on BASIC which Microsoft developed for use in their office products.

I started programming in BASIC on my Commodore 64 and 128 way back when. As I understand it Microsoft is no longer developing VBA in its Office suite and has designated a new scripting language (New script or something like that) however VBA will be around for a long time yet.

You can create forms in it for entering data or finding data into a spreadsheet or click a button to transfer data to a Word file for example.

This is my Seniors Club Membership file for example and the form that pops up to enter a new member. When you click on the Enter Members Details button VBA finds the next empty line in the appropriate spreadsheet, enters the data and then sorts the file into alphabetical order



To the right of the form you can see buttons that perform other functions using VBA including creating membership lists in Word. The form hides a lot of functions.

BTW if you have a copy of Excel just press ALT F11 and you will find yourself in the Editor for VBA within Excel. It turns excel from a useful program into a very powerful programming tool.

There are lots of videos on VBA on Youtube - Wise Owl does the best in my opinion.

If I was starting now I would learn Python, Java or something similar but I have decided that VBA and Batch will have to last me out.

BTW batch files date back to the days of DOS but perform functions much quicker and easier than any GUI with a minimal number of commands. I probably use batch files more than VBA these days eg all my backups are done using Batch files (they are plain text files)

Re: Covid and Excel VBA

I forgot to say that BASIC stands for: Beginners' All-purpose Symbolic Instruction Code.

It is something a person can read and which is then compiled into a language a computer can understand.

Its a bit old school these days

Re: Covid and Excel VBA

Thank you Bruce, it is still beyond me, computers like now were not even known about when I went to school.

all I have on my computer is " wordpad"

Re: Covid and Excel VBA

I still have my slide rule from when I was at school though actually I preferred log tables.

Re: Covid and Excel VBA

I have slightly refined the original modules with some minor error checking and combined then into just two modules.

At the moment you have to wait for the backgound query to finish before getting it to clean up and write the new data to a text file. It is probably possible to automatically wait for the query to finish before it moves on to the next process but as it is hardly arduous I haven't bothered.

The first module downloads the data and puts it in a new spreadsheet:

-----------------

Option Explicit

Sub GetTable()

Dim ws As Worksheet
Dim qt As QueryTable
Dim URL As String
Dim DateName As String
Dim bCheck As Boolean

URL = "https://www.worldometers.info/coronavirus/"

'create name for spreadsheet

DateName = Format(Date, "dd mmm yyyy")

'Check if sheet already exists

On Error Resume Next

bCheck = Len(Sheets(DateName).Name) > 0

On Error GoTo 0

If bCheck = True Then Exit Sub

'create a new sheet for the data

Set ws = Worksheets.Add(Before:=Sheets(1))

ws.Name = DateName

'get the data from their web site

Set qt = ws.QueryTables.Add(Connection:="URL;" & URL, Destination:=Range("A1"))

With qt
.WebFormatting = xlWebFormattingRTF
.Name = DateName
.WebSelectionType = xlAllTables
.WebTables = 1
.Refresh
End With


Worksheets("Interface").Activate

End Sub

-------------

The second part tidies up the data, finds the information required and writes it to a text file

-------------

Option Explicit

Sub GetCountryData()

Dim SearchRange As Range
Dim Country As Range
Dim DataInfo As String
Dim RevDate As String
Dim FinalData(1 To 4, 1 To 4) As Variant
Dim Dim1 As Integer, Dim2 As Integer
Dim MyFile As String

Application.ScreenUpdating = False

If Sheets(1).Range("B3").Value = "North America" Then
Sheets(1).Range("a3:s9").Delete
Sheets(1).Range("p:s").EntireColumn.Delete
End If


' Activate the new sheet and find its length

Sheets(1).Activate

'freeze headers

With ActiveWindow

If .FreezePanes Then .FreezePanes = False
.SplitColumn = 0
.SplitRow = 3
.FreezePanes = True
End With

Set SearchRange = Sheets(1).Range("b1", Range("b3").End(xlDown))

'get the world data first and write into an array

Set Country = SearchRange.Find(what:="World", MatchCase:=True, lookat:=xlWhole)

FinalData(1, 1) = Country.Offset(0, 1).Value
FinalData(1, 2) = Country.Offset(0, 3).Value

'get individual country data from list in "Interface" sheet and add to array

For Dim1 = 2 To 4

DataInfo = Worksheets("Interface").Range("a1").Offset((Dim1 - 2), 0).Value

Set Country = SearchRange.Find(what:=DataInfo, MatchCase:=True, lookat:=xlWhole)

If Country Is Nothing Then Exit For

FinalData(Dim1, 1) = Country.Offset(0, 1).Value
FinalData(Dim1, 2) = Country.Offset(0, 3).Value
FinalData(Dim1, 3) = Country.Offset(0, 8).Value
FinalData(Dim1, 4) = Country.Offset(0, 9).Value

Next Dim1


'create text fle to write data to

MyFile = Worksheets("Interface").Range("A20").Value

If Right(MyFile, 1) <> "\" Then MyFile = MyFile & "\"

RevDate = Format(Date, "yymmdd")

MyFile = MyFile & RevDate & " Covid Data.txt"

Open MyFile For Output As #1

'Start writing data to text file

'Date Title

Print #1, "Covid data for " & Format(Date, "Long Date") & ""
Print #1,

'Global Data

Print #1, "Global Cases " & Format(FinalData(1, 1), "#,###,##0")
Print #1, "Global Deaths " & Format(FinalData(1, 2), "#,###,##0")

Print #1,

'get data from array to write to sheet

For Dim1 = 2 To 4

Print #1, "" & Worksheets("Interface").Cells(Dim1 - 1, 1).Value & ""

For Dim2 = 1 To 4

Print #1, Worksheets("Interface").Cells((Dim2 + 3), 1).Value & " " & Format(FinalData(Dim1, Dim2), "#,###,##0")

Next Dim2

Print #1,

Next Dim1

'Add URL

Print #1, "https://www.worldometers.info/coronavirus/"

Close #1

Erase FinalData

Worksheets("Interface").Activate

Application.ScreenUpdating = True

End Sub

-------------

You are more than welcome to copy them into your version of MS Excel, try them out and improve on them.

Re: Covid and Excel VBA

BTW The interface is still very simple and looks like this:

Re: Covid and Excel VBA

Just as a matter of interest did anybody try loading this code into their own copy of Excel? With what result?

I have tweaked it few times since the last one published here but not in any major way.

The only thing I was thinking of doing was getting it to show the increases by comparing the numbers to the previous day's results - it is just a matter of gathering the enthusiasm and the time.

I should send it off the UK NHS IT people shouldn't I?

Re: Covid and Excel VBA

Don't bother, till they get 16.x

Re: Covid and Excel VBA

I have changed the module that creates the text file. This now refers to the previous data and creates information relating to the changes over approximately 24 hours.

The figures may vary slightly from the official figures but I try to be consistent by getting the data at about the same time each day - ie between 2.30 to 3pm Eastern time (we have just gone into daylight saving so it is an hour later than before)

As always you are welcome to try this in your own copy of Excel and modify it as you wish. It is very crude and with only minor error checking so you are welcome to improve on it. Lines starting with ' are merely comments (like REM in DOS Basic) so that in the future I can remember what each bit does.


Sub GetCountryData()

'This version checks previous sheet and adds difference data to text file
' for cases and deaths

Dim SearchRange As Range
Dim YesterRange As Range
Dim Country As Range
Dim YesterCountry As Range
Dim DataInfo As String
Dim RevDate As String
Dim FinalData(1 To 4, 1 To 6) As Variant
Dim strSpaces As String
Dim Dim1 As Integer, Dim2 As Integer
Dim MyFile As String
Dim SheetDate As Date

Application.ScreenUpdating = False

If Sheets(1).Range("B3").Value = "North America" Then
Sheets(1).Range("a3:s9").Delete
Sheets(1).Range("p:s").EntireColumn.Delete
End If


' Activate the new sheet, freeze headers, find its length and designate range

Sheets(1).Activate

'freeze headers

With ActiveWindow

If .FreezePanes Then .FreezePanes = False
.SplitColumn = 0
.SplitRow = 3
.FreezePanes = True
End With

SheetDate = Sheets(1).Name 'get date from the actual sheet as accuracy check

Set SearchRange = Sheets(1).Range("b1", Range("b3").End(xlDown))

'Active yesterday's sheet, find its length and designate range

Sheets(2).Activate

Set YesterRange = Sheets(2).Range("b1", Range("b3").End(xlDown)) 'previous days data

'get the world data first and write into an array

'Array x,1 = Cases, x,2 = Deaths, x,3 = Critical Cases, x,4 = Cases/Million
' x,5 = Cases - Yesterday's Cases, x,6 = = Deaths - Yesterday's Deaths

Set Country = SearchRange.Find(what:="World", MatchCase:=True, lookat:=xlWhole)
Set YesterCountry = YesterRange.Find(what:="World", MatchCase:=True, lookat:=xlWhole)

FinalData(1, 1) = Country.Offset(0, 1).Value
FinalData(1, 2) = Country.Offset(0, 3).Value

FinalData(1, 5) = FinalData(1, 1) - YesterCountry.Offset(0, 1).Value 'New cases in 24 hours
FinalData(1, 6) = FinalData(1, 2) - YesterCountry.Offset(0, 3).Value 'New Deaths in 24 hours


'get individual country data from list in "Interface" sheet and add to array

For Dim1 = 2 To 4

DataInfo = Worksheets("Interface").Range("a1").Offset((Dim1 - 2), 0).Value

Set Country = SearchRange.Find(what:=DataInfo, MatchCase:=True, lookat:=xlWhole)

Set YesterCountry = YesterRange.Find(what:=DataInfo, MatchCase:=True, lookat:=xlWhole) 'set range for previous day


If Country Is Nothing Then Exit For



FinalData(Dim1, 1) = Country.Offset(0, 1).Value
FinalData(Dim1, 2) = Country.Offset(0, 3).Value
FinalData(Dim1, 3) = Country.Offset(0, 8).Value
FinalData(Dim1, 4) = Country.Offset(0, 9).Value



FinalData(Dim1, 5) = FinalData(Dim1, 1) - YesterCountry.Offset(0, 1).Value
FinalData(Dim1, 6) = FinalData(Dim1, 2) - YesterCountry.Offset(0, 3).Value


Next Dim1


'create text fle to write data to

Select Case Environ("COMPUTERNAME")

Case "BCL-LAPTOP"

MyFile = Worksheets("Interface").Range("A20").Value

Case "LEGION-PC"

MyFile = Worksheets("Interface").Range("A21").Value

End Select

If Right(MyFile, 1) <> "\" Then MyFile = MyFile & "\"

RevDate = Format(SheetDate, "yymmdd")

MyFile = MyFile & RevDate & " Covid Data.txt"

strSpaces = " " 'add seperator to text (OFF Deletes extra spaces and tabs)

Open MyFile For Output As #1

'Start writing data to text file

'Date Title using Sheet Name as Date

Print #1, "[i]Covid data for " & Format(SheetDate, "Long Date") & "[/i]"
Print #1,

'Global Data

Print #1, "Global Cases: " & Format(FinalData(1, 1), "#,###,##0")
Print #1, strSpaces & "Increase: " & Format(FinalData(1, 5), "#,###,##0")
Print #1, "Global Deaths: " & Format(FinalData(1, 2), "#,###,##0")
Print #1, strSpaces & "Increase: " & Format(FinalData(1, 6), "#,###,##0")

Print #1,

'get data from array to write to sheet

For Dim1 = 2 To 4

Print #1, "[b]" & Worksheets("Interface").Cells(Dim1 - 1, 1).Value & "[/b]" 'print country name

For Dim2 = 1 To 4

If Dim2 < 3 Then

Print #1, Worksheets("Interface").Cells((Dim2 + 3), 1).Value & " " & Format(FinalData(Dim1, Dim2), "#,###,##0") & strSpaces & "Change: " & Format(FinalData(Dim1, Dim2 + 4), "#,###,##0")


Else

Print #1, Worksheets("Interface").Cells((Dim2 + 3), 1).Value & " " & Format(FinalData(Dim1, Dim2), "#,###,##0")

End If

Next Dim2

Print #1,

Next Dim1

'Add URL

Print #1, "https://www.worldometers.info/coronavirus/"

'Close Text file

Close #1

'Get rid of data and array

Erase FinalData


Worksheets("Interface").Activate

Application.ScreenUpdating = True

End Sub