Get the serial number, computer name, user name, email address, vendor, model, IP address, domain suffix, and OS version from the command line.

I developed this script to do a PC inventory and save it to a network share:

       
@echo off
cls
for /f "tokens=2 delims==" %%F in ('wmic csproduct get vendor /format:value') do set vendor=%%F
set vendor=%vendor:,=%
for /f "tokens=2 delims==" %%F in ('wmic csproduct get Name /format:value') do set model=%%F
set model=%model:,=%
for /f "tokens=2 delims==" %%F in ('wmic csproduct get IdentifyingNumber /format:value') do set serial=%%F
for /f "tokens=2 delims== usebackq" %%F in (`wmic os get version /format:value`) do set osver=%%F
for /f "tokens=2 delims== usebackq" %%F in (`wmic nicconfig where "description like '%%ethernet%%'" get DHCPEnabled /format:value`) do set dhcpether=%%F
if %dhcpether% == TRUE (
 set ipaddress=DHCP 
) else ( 
 for /f "tokens=2 delims== usebackq" %%F in (`wmic nicconfig where "description like '%%ethernet%%'" get IPAddress /format:value`) do set ipaddress=%%F
)
for /f "tokens=2 delims== usebackq" %%F in (`wmic nicconfig where "description like '%%ethernet%%'" get dnsdomain /format:value`) do set etherdomain=%%F
for /f "tokens=2 delims== usebackq" %%F in (`wmic nicconfig where "description like '%%wireless%%'" get dnsdomain /format:value`) do set wifidomain=%%F
for /f "tokens=*" %%F in ('whoami /UPN') do set email=%%F
echo %serial%,%computername%,%username%,%email%,%vendor%,%model%,%ipaddress%,%etherdomain%,%wifidomain%,%osver% >> \\servername\sharename\pc-inventory.csv
cls
echo Hostname=%computername%
echo Script completed successfully.
pause 

       
 

Excel VBA data connection code to join two worksheets in a SQL query.

Assume we have an Excel workbook with a Customer Item cross reference, and a second Excel workbook listing sales orders using our own item number.  If we want to see a report that shows sales orders by the customer item numbers we will need to create a query joining the two workbooks by our item number.

First let's add a function to create a worksheet and add a query.  We'll pass the SQL query and Table name to it in the arguments.  Note that the data source is self referencing.  The actual data location is defined in the SQL argument:

Function CreateQuery(SQL, TableName)
'Create a Query on a new worksheet
'We are going to "cheat" and use the active workbook for the DSN
'The SQL string defines the worksheets from which we load the actual data

    'delete any existing sheets with this name
    Application.DisplayAlerts = False
    On Error Resume Next
    Sheets(TableName).Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
   
    'add a new sheet
    ActiveWorkbook.Worksheets.Add
    ActiveSheet.Name = TableName '<-Second function argument
   
    'Create the query in cell A1
    With ActiveSheet.ListObjects.Add(SourceType:=0, _
        Source:="ODBC;DSN=Excel Files;" & _
        "DBQ=" & ActiveWorkbook.FullName & ";" & _
        "DefaultDir=" & ActiveWorkbook.Path & ";" & _
        "DriverId=1046;MaxBufferSize=2048;PageTimeout=5;", _
        Destination:=Range("$A$1")).QueryTable
        .CommandText = SQL '<-First function argument
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = TableName '<-Second function argument
        .Refresh BackgroundQuery:=False
    End With
    Range("A2").Select
End Function
 
Next we'll create a sub that will pass the SQL string and the Table name to the function we created.  Note that the SQL string in this sub uses a query that joins two separate spreadsheets:

Sub Main()
    Dim SQL As String
    Dim QueryResult As Variant
    Dim FirstPath As String
    Dim SecondPath As String

    'Get the files we want to use
    Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
    Application.FileDialog(msoFileDialogOpen).Title = "Select the First Workbook"
    Application.FileDialog(msoFileDialogOpen).InitialFileName = "C:\YourPath\FirstWorkbook.xlsx"
    FirstPath = Application.FileDialog(msoFileDialogOpen).Show
    If FirstPath <> 0 Then
        'The FirstPath is the Customer Item cross reference workbook
        FirstPath = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
    Else
        'bail out if no file is selected
        Exit Sub
    End If
    Application.FileDialog(msoFileDialogOpen).Title = "Select the Second Workbook"
    Application.FileDialog(msoFileDialogOpen).InitialFileName = "C:\YourPath\SecondWorkbook.xlsx"
    SecondPath = Application.FileDialog(msoFileDialogOpen).Show
   
    If SecondPath <> 0 Then
        'The SecondPath is the sales order line items workbook
        SecondPath = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
    Else
        'bail out if no file is selected
        Exit Sub
    End If
       
    'Create the First Query Table
    SQL = "SELECT SECOND.Material, FIRST.CustItem, SECOND.Description, SECOND.Order, SECOND.Price, SECOND.Qty " & _
        "FROM `" & SecondPath & "`.`Sheet1$` SECOND " & _
        "LEFT JOIN `" & FirstPath & "`.`Sheet1$` FIRST " & _
        "ON SECOND.Material=FIRST.Material " & _
        "WHERE SECOND.Customer = 'AAA Wash Basins' " & _
        "ORDER BY FIRST.CustItem, SECOND.Material"
       
    'now we run our function to create the query
    QueryResult = CreateQuery(SQL, "CustomerOrders")
    'Add the total rows to the Table (optional)
    ActiveSheet.ListObjects("CustomerOrders").ShowTotals = True
    ActiveSheet.ListObjects("CustomerOrders").ListColumns("Order"). _
        TotalsCalculation = xlTotalsCalculationSum
    ActiveSheet.ListObjects("CustomerOrders").ListColumns("Qty").TotalsCalculation _
        = xlTotalsCalculationSum
   
End Sub

Why are network links disappearing from my desktop?

If you have network links that are disappearing from your desktop, it may be that a default Windows 7 maintenance task is removing them.  (without asking, yay!)  You can disable the task by one of the following methods:

1) Disabling it in Scheduled Tasks. 

2) Disable it from the command line with the following command:

schtasks /Change /TN \Microsoft\Windows\Diagnosis\Scheduled /DISABLE

View source code quickly with windows preview

If you want to view your source code in windows preview you will need to add two items to the registry entry for that file type.  Open your registry editor (window key+R, regedit.exe) and find the class definition for your file type.  In the registry editor it will be under Computer\HKEY_CLASSES_ROOT\.  The example I'm using is for Progress 4GL .p source code files.


Then you will need to add a string named "Content Type" with a value of text/plain and another string named "PerceivedType" with a value of text.

Now you can see your code in the preview window.