Protect Yourself From Tech Support Scams
October 14, 2019
Protect Yourself From Tech Support Scams
Tech support scams are an industry-wide issue where scammers trick you into paying for unnecessary technical support services. You can help protect yourself from scammers by verifying that the contact is a
Microsoft Agent or Microsoft Employee and that the phone number is an
official Microsoft global customer service number.
Thanks; works as required. Can you please point out a reference (or explain) to help understand what is going on in the code.
I am not sure how well I can explain this for you but I will try. If anyone else reads this and either disagrees with any of my explanation or can explain better then feel free to post.
Firstly, in the code example below, I have modified the declaration of the variables. Instead of using the generic Object, it is better to use their proper description. The reason is that the VBA intellisense kicks in which helps with the programming.
(More on intellisense below.)
Next paragraph added with edit after initial post.
To use the proper declarations you will need to open the VBA editor and select Tools -> References and scroll down until you find Microsoft Scripting Runtime and check the box and click OK. (Ensure you check the box; not just select the line.)
This is individual to the particular workbook; does not remain for all workbooks.
strDir = "C:\Users\OssieMac\Documents\Excel\Test Macros"...... Should be self explanatory.
Set fso = CreateObject("Scripting.FileSystemObject")........ Creates an object of the file system of the computer and assigns to the variable fso and the variable is then used to address the file system on the computer. (Note the line begins with 'Set'
when assigning an object to a variable.)
Set objFiles = fso.GetFolder(strDir).Files......... Assigns the files in the specified directory to the variable objFiles and the variable is then used in lieu of the full description. This variable contains the full list of files in the directory.
The following 5 lines of code iterate through the files in objFiles and outputs them to the active sheet. Note that I have enhanced this to my previous code to exclude temporary excel files that are prefixed with the tild and dollar signs (~$).
If Not objF.Name Like "~$*" Then..... Excludes temporary file created when excel workbook is opened. These temporary files are prefixed with the tild and dollar sign (~$). These will normally only occur in ThisWorkbook.Path. However, it is possible
for them to be left over from files when Excel crashes and the workbook does not close properly.
ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)........ Finds the next blank cell in column A. It is like when on the worksheet, selecting the last cell in column A and then holding the Ctrl key and pressing the up arrow. The cursor will
go up to the first cell it finds with data which is actually the last cell in column A containing data. Offset(1, 0) then moves it down one cell which will be a blank cell.
The following 3 lines is just good housekeeping in that it cleans up the object variables when they are no longer required. Set them to nothing in the reverse order to that which is used to assign objects to them.
Set objF = Nothing
Set objFiles = Nothing
Set fso = Nothing
Now Intellisense. It is when you are programming and place a dot (.) ready to continue with the next sector of code, you get a drop down with possible options. The variables need to be declared correctly instead of declaring as the generic Object.
Open a new workbook and save the workbook in a folder where there are other files. (Must save workbook otherwise ThisWorkbook.Path will error. If using xl2007 or later then save as Macro enabled workbook.). Copy the entire code from below into
the VBA editor and try the following examples: Don't forget to set the References as per my edit above.
In this line Set objFiles = fso.GetFolder(strDir).Files......... Delete the .Files from the end and then type the dot and see the dropdown of options.
In this line lngFileCount = objFiles.Count....... Delete the .Count and then re-type the dot.
In this line ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = objF.Name
Delete the .Name from the end and re-type the dot.