top of page

It's not so easy to export data from Excel to a load file with a quote qualifier and comma delimiter - the format required by many applications. Saving the file in the .csv format will omit the quotation marks:




. . . it's a pain to accurately add in the missing quotation marks by altering the file in a text editor, and Excel doesn't provide an easy way to add them in the export. Luckily, Microsoft has posted a macro here, which will export data from an Excel spreadsheet in the correct format.



Enter the below code in a module for the worksheet with the data:



Select the data on the worksheet that you want to export to a new file. Run the macro and it will prompt you to enter a path for the new file:




A new file with comma delimiters and quotation mark qualifiers appears:



This is the complete code but it is not formatted correctly so use the code at this link or edit the code before running it.


Sub QuoteCommaExport()

' Dimension all variables.

Dim DestFile As String

Dim FileNum As Integer

Dim ColumnCount As Long

Dim RowCount As Long

' Prompt user for destination file name.

DestFile = InputBox("Enter the destination filename" _

& Chr(10) & "(with complete path):", "Quote-Comma Exporter")

' Obtain next free file handle number.

FileNum = FreeFile()

' Turn error checking off.

On Error Resume Next

' Attempt to open destination file for output.

Open DestFile For Output As #FileNum

' If an error occurs report it and end.

If Err <> 0 Then

MsgBox "Cannot open filename " & DestFile

End

End If

' Turn error checking on.

On Error GoTo 0

' Loop for each row in selection.

For RowCount = 1 To Selection.Rows.Count

' Loop for each column in selection.

For ColumnCount = 1 To Selection.Columns.Count

' Write current cell's text to file with quotation marks.

Print #FileNum, """" & Selection.Cells(RowCount, _

ColumnCount).Text & """";

' Check if cell is in last column.

If ColumnCount = Selection.Columns.Count Then

' If so, then write a blank line.

Print #FileNum,

Else

' Otherwise, write a comma.

Print #FileNum, ",";

End If

' Start next iteration of ColumnCount loop.

Next ColumnCount

' Start next iteration of RowCount loop.

Next RowCount

' Close destination file.

Close #FileNum

End Sub

69 views0 comments

Keep in mind that the recommended minimum internet speed for an online video conference is only about 4.0 to 5.0 megabytes per second - not very high by today's standards.


A technical guide posted here by Zoom, shows that you'll need a stronger speed for a group session, as opposed to a one on one video call. An audio call should be possible at 100 kilobytes per second, and screen sharing only requires about twice as much speed ~200 kbps.


The University of Chicago Data Science Institute conducted a test to measure the amount of bandwidth required for Google Meet, Microsoft Teams, and Zoom video conferences and determined that they averaged about 1-2 mbps download, and 0.75-1.4 mbps upload. Teams tends to take up more bandwidth than the other applications.



Not much difference. However on a small local network when multiple video calls are active, choosing Zoom over its competitors can make a decisive difference.




Teams also took longer to recover from an interruption in a WiFi signal than Zoom or Meet.


The study also found that systems may give priority to the first call that is initiated.

20 views0 comments

If you want help getting a list of last names or other proper nouns which may be keywords in a long text excerpt, you can make use of the below Visual Basic macro which will generate a list of words that MS Word will identify as possible spelling errors.


If you simply plug in the code in a new module in Visual Basic:



. . . the macro will review a Word document:




. . . and output a list of the words which are not in the spell check dictionary:



Thanks to Jay Freedman for posting this macro here. [Copy the text from the post on the Microsoft site, or remove the extra blank lines that result when you paste this code into Visual Basic to make it work.]


Sub ListSpellingErrors()

Dim inDoc As Document

Dim outDoc As Document

Dim er As Range

Set inDoc = ActiveDocument

If ActiveDocument.SpellingErrors.Count > 0 Then

Set outDoc = Documents.Add

outDoc.Sections(1).Headers(wdHeaderFooterPrimary) _

.Range.Text = "Spelling errors in " & inDoc.FullName

Else

MsgBox "There are no spelling errors in this document."

Exit Sub

End If

For Each er In inDoc.SpellingErrors

outDoc.Range.InsertAfter er.Text & vbCr

Next er

' optionally, to sort the output,

' remove the quote mark from the next line

' outDoc.Range.Sort

End Sub





37 views0 comments
bottom of page