Ian_Clubb
asked on
WMI hardware inventory
Hi
Below is a script that references WMI to create a spreadsheet. It checks the serial number of the PC and if it is found in the spreadsheet it overites the row, it it is a new entry it uses the next available row.
I run into problems when multiple users write to the file at once, even with the workbook shared it corrupts regularly.
I would like it to function in the same way with regards to the serial number check and what info it retrieves form WMI, but write it into a table of an access database not an excel file.
I have no scripting knowledge and this script was wrote by someone on this site.
Any help would be appreciated.
Thanks
Ian
'On Error Resume Next
' To convert to a logon script that runs without user interaction, add a rem in front of the strComputer = InputBox
' line below and remove the rem from the strComputer = "." line below that. This will then only check the PC on which
' the script runs
Set WshNetwork = WScript.CreateObject("WScr ipt.Networ k")
strComputer = "."
strWorkBook = "\\eastcotefps\assetscript $\asset.xl s"
' Create Excel Spreadsheet
Set app = CreateObject("Excel.Applic ation")
app.Workbooks.Open strWorkBook
Set wb = app.ActiveWorkbook
app.Visible = False
wb.Activate
Set ws = wb.Worksheets(1)
ws.Cells(1,1).Value = "Computername"
ws.Columns(1).ColumnWidth = 20
ws.Cells(1,2).Value = "Username"
ws.Columns(2).ColumnWidth = 30
ws.Cells(1,3).Value = "Manufacturer"
ws.Columns(3).ColumnWidth = 20
ws.Cells(1,4).Value = "Model"
ws.Columns(4).ColumnWidth = 20
ws.Cells(1,5).Value = "Serial Number"
ws.Columns(5).ColumnWidth = 30
ws.Cells(1,6).Value = "CPU"
ws.Columns(6).ColumnWidth = 30
ws.Cells(1,7).Value = "CPU Speed"
ws.Columns(7).ColumnWidth = 10
ws.Cells(1,8).Value = "Operating System"
ws.Columns(8).ColumnWidth = 40
ws.Cells(1,9).Value = "Service Pack"
ws.Columns(9).ColumnWidth = 20
ws.Cells(1,10).Value = "Total Memory"
ws.Columns(10).ColumnWidth = 20
ws.Cells(1,11).Value = "Audit Date"
ws.Columns(11).ColumnWidth = 20
ws.Rows(1).Font.Bold = True
' Get the serial number first to see if it already exists in the spreadsheet
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("S elect * from Win32_BIOS",,48)
For Each objItem In colItems
strSerialNumber = "" & objItem.SerialNumber
Next
Set objWMIService = Nothing
Set colItems = Nothing
intRowToUse = -1
For intRowCount = 2 To ws.UsedRange.Rows.Count
If Trim(strSerialNumber) = Trim(ws.Cells(intRowCount, 5).Value) Then
intRowToUse = intRowCount
End If
Next
If intRowToUse = -1 Then
intRowToUse = ws.UsedRange.Rows.Count + 1
End If
' Get Computer System Details
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("S elect * from Win32_ComputerSystem",,48)
For Each objItem In colItems
ws.Cells(intRowToUse,1).Va lue = "" & objItem.Caption
ws.Cells(intRowToUse,2).Va lue = "" & objItem.UserName
ws.Cells(intRowToUse,3).Va lue = "" & objItem.Manufacturer
ws.Cells(intRowToUse,4).Va lue = "" & objItem.Model
Next
Set objWMIService = Nothing
Set colItems = Nothing
'
'Output the Serial Number
ws.Cells(intRowToUse,5).Va lue = strSerialNumber
'
' Get CPU Details
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("S elect * from Win32_Processor",,48)
For Each objItem In colItems
ws.Cells(intRowToUse,6).Va lue = "" & objItem.Name
ws.Cells(intRowToUse,7).Va lue = "" & objItem.CurrentClockSpeed
Next
Set objWMIService = Nothing
Set colItems = Nothing
'
' Get OS Details
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("S elect * from Win32_OperatingSystem",,48 )
For Each objItem In colItems
ws.Cells(intRowToUse,8).Va lue = "" & objItem.Caption
ws.Cells(intRowToUse,9).Va lue = "" & objItem.CSDVersion
ws.Cells(intRowToUse,10).V alue = "" & FormatNumber(objItem.Total VisibleMem orySize/10 24,0)
Next
Set objWMIService = Nothing
Set colItems = Nothing
'
' Get & Writeout current Date
ws.Cells(intRowToUse,11).v alue = "" & Day(Now) & "-" & Month(Now) & "-" & Year(Now)
'Autofit all columns
app.ActiveSheet.Columns.En tireColumn .AutoFit
' Save Audit File
app.DisplayAlerts = False
'wb.Saved = True
wb.Close True
app.DisplayAlerts = False
app.quit
MsgBox "Done"
Below is a script that references WMI to create a spreadsheet. It checks the serial number of the PC and if it is found in the spreadsheet it overites the row, it it is a new entry it uses the next available row.
I run into problems when multiple users write to the file at once, even with the workbook shared it corrupts regularly.
I would like it to function in the same way with regards to the serial number check and what info it retrieves form WMI, but write it into a table of an access database not an excel file.
I have no scripting knowledge and this script was wrote by someone on this site.
Any help would be appreciated.
Thanks
Ian
'On Error Resume Next
' To convert to a logon script that runs without user interaction, add a rem in front of the strComputer = InputBox
' line below and remove the rem from the strComputer = "." line below that. This will then only check the PC on which
' the script runs
Set WshNetwork = WScript.CreateObject("WScr
strComputer = "."
strWorkBook = "\\eastcotefps\assetscript
' Create Excel Spreadsheet
Set app = CreateObject("Excel.Applic
app.Workbooks.Open strWorkBook
Set wb = app.ActiveWorkbook
app.Visible = False
wb.Activate
Set ws = wb.Worksheets(1)
ws.Cells(1,1).Value = "Computername"
ws.Columns(1).ColumnWidth = 20
ws.Cells(1,2).Value = "Username"
ws.Columns(2).ColumnWidth = 30
ws.Cells(1,3).Value = "Manufacturer"
ws.Columns(3).ColumnWidth = 20
ws.Cells(1,4).Value = "Model"
ws.Columns(4).ColumnWidth = 20
ws.Cells(1,5).Value = "Serial Number"
ws.Columns(5).ColumnWidth = 30
ws.Cells(1,6).Value = "CPU"
ws.Columns(6).ColumnWidth = 30
ws.Cells(1,7).Value = "CPU Speed"
ws.Columns(7).ColumnWidth = 10
ws.Cells(1,8).Value = "Operating System"
ws.Columns(8).ColumnWidth = 40
ws.Cells(1,9).Value = "Service Pack"
ws.Columns(9).ColumnWidth = 20
ws.Cells(1,10).Value = "Total Memory"
ws.Columns(10).ColumnWidth
ws.Cells(1,11).Value = "Audit Date"
ws.Columns(11).ColumnWidth
ws.Rows(1).Font.Bold = True
' Get the serial number first to see if it already exists in the spreadsheet
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("S
For Each objItem In colItems
strSerialNumber = "" & objItem.SerialNumber
Next
Set objWMIService = Nothing
Set colItems = Nothing
intRowToUse = -1
For intRowCount = 2 To ws.UsedRange.Rows.Count
If Trim(strSerialNumber) = Trim(ws.Cells(intRowCount,
intRowToUse = intRowCount
End If
Next
If intRowToUse = -1 Then
intRowToUse = ws.UsedRange.Rows.Count + 1
End If
' Get Computer System Details
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("S
For Each objItem In colItems
ws.Cells(intRowToUse,1).Va
ws.Cells(intRowToUse,2).Va
ws.Cells(intRowToUse,3).Va
ws.Cells(intRowToUse,4).Va
Next
Set objWMIService = Nothing
Set colItems = Nothing
'
'Output the Serial Number
ws.Cells(intRowToUse,5).Va
'
' Get CPU Details
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("S
For Each objItem In colItems
ws.Cells(intRowToUse,6).Va
ws.Cells(intRowToUse,7).Va
Next
Set objWMIService = Nothing
Set colItems = Nothing
'
' Get OS Details
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("S
For Each objItem In colItems
ws.Cells(intRowToUse,8).Va
ws.Cells(intRowToUse,9).Va
ws.Cells(intRowToUse,10).V
Next
Set objWMIService = Nothing
Set colItems = Nothing
'
' Get & Writeout current Date
ws.Cells(intRowToUse,11).v
'Autofit all columns
app.ActiveSheet.Columns.En
' Save Audit File
app.DisplayAlerts = False
'wb.Saved = True
wb.Close True
app.DisplayAlerts = False
app.quit
MsgBox "Done"
ASKER
I'm sorry i dont understand what your getting at, this is to perform a hardware inventory on all the machines on my network, i have it running as a scheduled task every hour. Currently it writes to an excel file, problem is it keeps corrupting. I want to do away with the excel file completely and have the data go straight into an access database.
The other problem is I have no scripting knowledge what-so-ever..........
The other problem is I have no scripting knowledge what-so-ever..........
<even with the workbook shared it corrupts regularly.>
That's what shared workbooks do best :-(
How many people are your talking about logging in at the same time?
That's what shared workbooks do best :-(
How many people are your talking about logging in at the same time?
ASKER
up to 400 when I role it out company wide
A database could be overkill.....what about an alternative.....
What I am thinking of is:
1) Have a dedicated network share for the list of files for the Hardware Inventory
2) On each hour, each PC writes it's inventory information to this share, but writes a flat text file with the computer name, or in your case, probably the serial number, as the file name. That should overcome any multiple access problems.
3) A separate script that then be run manually, or maybe ten minutes after each hour ( to allow for slow machines) that will iterate through every file in that dedicated share, and pull them all together in an excel spreadsheet. That way, only one process will be writing to the spreadsheet, which would probably be a scheduled task on that server that houses the share.
If that sounds like a good option for you, I can work on that script.
Regards,
Rob.
What I am thinking of is:
1) Have a dedicated network share for the list of files for the Hardware Inventory
2) On each hour, each PC writes it's inventory information to this share, but writes a flat text file with the computer name, or in your case, probably the serial number, as the file name. That should overcome any multiple access problems.
3) A separate script that then be run manually, or maybe ten minutes after each hour ( to allow for slow machines) that will iterate through every file in that dedicated share, and pull them all together in an excel spreadsheet. That way, only one process will be writing to the spreadsheet, which would probably be a scheduled task on that server that houses the share.
If that sounds like a good option for you, I can work on that script.
Regards,
Rob.
ASKER
Sounds good, thanks very much
OK, so here's the script to write the same info to a text file with a name of the serial number. Just change the UNC path to match that in your environment. I will work on the combining of the scripts into a spreadsheet tomorrow. I'm going home now.
'==============
Option Explicit
'On Error Resume Next
Dim objFSO, wshNetwork, strComputer, objWMIService, colItems, strSerialNumber, strFileName, strResults, objItem, objOutputFile
Set objFSO = CreateObject("Scripting.Fi leSystemOb ject")
' To convert to a logon script that runs without user interaction, add a rem in front of the strComputer = InputBox
' line below and remove the rem from the strComputer = "." line below that. This will then only check the PC on which
' the script runs
Set WshNetwork = WScript.CreateObject("WScr ipt.Networ k")
strComputer = "."
' Get the serial number first to see if it already exists in the spreadsheet
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("S elect * from Win32_BIOS",,48)
For Each objItem In colItems
strSerialNumber = "" & Trim(objItem.SerialNumber)
Next
Set objWMIService = Nothing
Set colItems = Nothing
strFileName = "\\NTFP\Rsampson\Scripting \Test Scripts\Hardware Inventory Script\" & strSerialNumber & ".txt"
' Create Excel Spreadsheet
strResults = "Computername;Username;Man ufacturer; Model;Seri al Number;CPU;CPU Speed;Operating System;Service Pack;Total Memory;Audit Date" & VbCrLf
' Get Computer System Details
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("S elect * from Win32_ComputerSystem",,48)
For Each objItem In colItems
strResults = strResults & Trim(objItem.Caption) & ";" & Trim(objItem.UserName) & ";" & Trim(objItem.Manufacturer) & ";" & Trim(objItem.Model)
Next
Set objWMIService = Nothing
Set colItems = Nothing
'
'Output the Serial Number
strResults = strResults & ";" & strSerialNumber
'
' Get CPU Details
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("S elect * from Win32_Processor",,48)
For Each objItem In colItems
strResults = strResults & ";" & Trim(objItem.Name) & ";" & Trim(objItem.CurrentClockS peed)
Next
Set objWMIService = Nothing
Set colItems = Nothing
'
' Get OS Details
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("S elect * from Win32_OperatingSystem",,48 )
For Each objItem In colItems
strResults = strResults & ";" & Trim(objItem.Caption) & ";" & Trim(objItem.CSDVersion) & ";" & Trim(FormatNumber(objItem. TotalVisib leMemorySi ze/1024,0) )
Next
Set objWMIService = Nothing
Set colItems = Nothing
'
' Get & Writeout current Date
strResults = strResults & ";" & Trim(Day(Now) & "-" & Month(Now) & "-" & Year(Now))
Set objOutputFile = objFSO.CreateTextFile(strF ileName, True)
objOutputFile.Write strResults
objOutputFile.Close
Set objOutputFile = Nothing
Set objFSO = Nothing
MsgBox "Done"
'==================
'==============
Option Explicit
'On Error Resume Next
Dim objFSO, wshNetwork, strComputer, objWMIService, colItems, strSerialNumber, strFileName, strResults, objItem, objOutputFile
Set objFSO = CreateObject("Scripting.Fi
' To convert to a logon script that runs without user interaction, add a rem in front of the strComputer = InputBox
' line below and remove the rem from the strComputer = "." line below that. This will then only check the PC on which
' the script runs
Set WshNetwork = WScript.CreateObject("WScr
strComputer = "."
' Get the serial number first to see if it already exists in the spreadsheet
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("S
For Each objItem In colItems
strSerialNumber = "" & Trim(objItem.SerialNumber)
Next
Set objWMIService = Nothing
Set colItems = Nothing
strFileName = "\\NTFP\Rsampson\Scripting
' Create Excel Spreadsheet
strResults = "Computername;Username;Man
' Get Computer System Details
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("S
For Each objItem In colItems
strResults = strResults & Trim(objItem.Caption) & ";" & Trim(objItem.UserName) & ";" & Trim(objItem.Manufacturer)
Next
Set objWMIService = Nothing
Set colItems = Nothing
'
'Output the Serial Number
strResults = strResults & ";" & strSerialNumber
'
' Get CPU Details
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("S
For Each objItem In colItems
strResults = strResults & ";" & Trim(objItem.Name) & ";" & Trim(objItem.CurrentClockS
Next
Set objWMIService = Nothing
Set colItems = Nothing
'
' Get OS Details
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("S
For Each objItem In colItems
strResults = strResults & ";" & Trim(objItem.Caption) & ";" & Trim(objItem.CSDVersion) & ";" & Trim(FormatNumber(objItem.
Next
Set objWMIService = Nothing
Set colItems = Nothing
'
' Get & Writeout current Date
strResults = strResults & ";" & Trim(Day(Now) & "-" & Month(Now) & "-" & Year(Now))
Set objOutputFile = objFSO.CreateTextFile(strF
objOutputFile.Write strResults
objOutputFile.Close
Set objOutputFile = Nothing
Set objFSO = Nothing
MsgBox "Done"
'==================
The UNC path for you to change is:
strFileName = "\\NTFP\Rsampson\Scripting \Test Scripts\Hardware Inventory Script\" & strSerialNumber & ".txt"
Regards,
Rob.
strFileName = "\\NTFP\Rsampson\Scripting
Regards,
Rob.
By the way, if anyone is interested, I use almost exactly the same method (but with much less information) to write each user's User login name, IP Address, Computername, and date and time to a UNC text file that is called their login name, appending each instance of a login to the top of the file. Then with these files I know exactly where this person logged on and when.
Regards,
Rob.
Regards,
Rob.
Whoops, I've just noticed that it outputs the CPU details twice....must be the dual core processor in my system. Use this full code version instead:
'========================= ==
Option Explicit
'On Error Resume Next
Dim objFSO, wshNetwork, strComputer, objWMIService, colItems, strSerialNumber, strFileName, strResults, objItem, objOutputFile, strCPUDetails
Set objFSO = CreateObject("Scripting.Fi leSystemOb ject")
' To convert to a logon script that runs without user interaction, add a rem in front of the strComputer = InputBox
' line below and remove the rem from the strComputer = "." line below that. This will then only check the PC on which
' the script runs
Set WshNetwork = WScript.CreateObject("WScr ipt.Networ k")
strComputer = "."
' Get the serial number first to see if it already exists in the spreadsheet
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("S elect * from Win32_BIOS",,48)
For Each objItem In colItems
strSerialNumber = "" & Trim(objItem.SerialNumber)
Next
Set objWMIService = Nothing
Set colItems = Nothing
strFileName = "\\NTFP\Rsampson\Scripting \Test Scripts\Hardware Inventory Script\" & strSerialNumber & ".txt"
' Create Excel Spreadsheet
strResults = "Computername;Username;Man ufacturer; Model;Seri al Number;CPU;CPU Speed;Operating System;Service Pack;Total Memory;Audit Date" & VbCrLf
' Get Computer System Details
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("S elect * from Win32_ComputerSystem",,48)
For Each objItem In colItems
strResults = strResults & Trim(objItem.Caption) & ";" & Trim(objItem.UserName) & ";" & Trim(objItem.Manufacturer) & ";" & Trim(objItem.Model)
Next
Set objWMIService = Nothing
Set colItems = Nothing
'
'Output the Serial Number
strResults = strResults & ";" & strSerialNumber
'
' Get CPU Details
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("S elect * from Win32_Processor",,48)
For Each objItem In colItems
strCPUDetails = Trim(objItem.Name) & ";" & Trim(objItem.CurrentClockS peed)
Next
Set objWMIService = Nothing
Set colItems = Nothing
strResults = strResults & ";" & strCPUDetails
'
' Get OS Details
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("S elect * from Win32_OperatingSystem",,48 )
For Each objItem In colItems
strResults = strResults & ";" & Trim(objItem.Caption) & ";" & Trim(objItem.CSDVersion) & ";" & Trim(FormatNumber(objItem. TotalVisib leMemorySi ze/1024,0) )
Next
Set objWMIService = Nothing
Set colItems = Nothing
'
' Get & Writeout current Date
strResults = strResults & ";" & Trim(Day(Now) & "-" & Month(Now) & "-" & Year(Now))
Set objOutputFile = objFSO.CreateTextFile(strF ileName, True)
objOutputFile.Write strResults
objOutputFile.Close
Set objOutputFile = Nothing
Set objFSO = Nothing
MsgBox "Done"
'=====================
Regards,
Rob.
'=========================
Option Explicit
'On Error Resume Next
Dim objFSO, wshNetwork, strComputer, objWMIService, colItems, strSerialNumber, strFileName, strResults, objItem, objOutputFile, strCPUDetails
Set objFSO = CreateObject("Scripting.Fi
' To convert to a logon script that runs without user interaction, add a rem in front of the strComputer = InputBox
' line below and remove the rem from the strComputer = "." line below that. This will then only check the PC on which
' the script runs
Set WshNetwork = WScript.CreateObject("WScr
strComputer = "."
' Get the serial number first to see if it already exists in the spreadsheet
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("S
For Each objItem In colItems
strSerialNumber = "" & Trim(objItem.SerialNumber)
Next
Set objWMIService = Nothing
Set colItems = Nothing
strFileName = "\\NTFP\Rsampson\Scripting
' Create Excel Spreadsheet
strResults = "Computername;Username;Man
' Get Computer System Details
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("S
For Each objItem In colItems
strResults = strResults & Trim(objItem.Caption) & ";" & Trim(objItem.UserName) & ";" & Trim(objItem.Manufacturer)
Next
Set objWMIService = Nothing
Set colItems = Nothing
'
'Output the Serial Number
strResults = strResults & ";" & strSerialNumber
'
' Get CPU Details
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("S
For Each objItem In colItems
strCPUDetails = Trim(objItem.Name) & ";" & Trim(objItem.CurrentClockS
Next
Set objWMIService = Nothing
Set colItems = Nothing
strResults = strResults & ";" & strCPUDetails
'
' Get OS Details
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("S
For Each objItem In colItems
strResults = strResults & ";" & Trim(objItem.Caption) & ";" & Trim(objItem.CSDVersion) & ";" & Trim(FormatNumber(objItem.
Next
Set objWMIService = Nothing
Set colItems = Nothing
'
' Get & Writeout current Date
strResults = strResults & ";" & Trim(Day(Now) & "-" & Month(Now) & "-" & Year(Now))
Set objOutputFile = objFSO.CreateTextFile(strF
objOutputFile.Write strResults
objOutputFile.Close
Set objOutputFile = Nothing
Set objFSO = Nothing
MsgBox "Done"
'=====================
Regards,
Rob.
ASKER
Thanks Rob that works well, can you ensure that when the text files are dumped into the spreadsheet that the serial number is checked and that it is either overitten or a new row is created.
Thanks for your help
Thanks for your help
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Rob
When I run the script to create the excel file excel opens and it changes the font to bold but then I get a runtime error, line 37, char 13, error "file not found" code 800A0035.
I've checked and double checked the paths I've put in, hope i'm not doing something stupid!!
Thanks
Ian
When I run the script to create the excel file excel opens and it changes the font to bold but then I get a runtime error, line 37, char 13, error "file not found" code 800A0035.
I've checked and double checked the paths I've put in, hope i'm not doing something stupid!!
Thanks
Ian
Hmmm, just under this line:
For Each objFile In objFSO.GetFolder(strInvent oryFolder) .Files
add these two lines
MsgBox "Looking in " & strInventoryFolder & " at " & vbCrLf & _
"file: " & objFile.Name
and you should see a message box appear for each file (maybe make sure there's only five or so files) that should tell you what folder it's looking in, and each filename that it will try to open on the next line.
I changed strInventoryFolder to a dot ".", just because I put the script itself in the same folder as these text files, so the dot just tells it to look int the current folder. If you don't have the script in the same folder, make sure you put in the full path (by UNC should work) to the text files.
Regards,
Rob.
For Each objFile In objFSO.GetFolder(strInvent
add these two lines
MsgBox "Looking in " & strInventoryFolder & " at " & vbCrLf & _
"file: " & objFile.Name
and you should see a message box appear for each file (maybe make sure there's only five or so files) that should tell you what folder it's looking in, and each filename that it will try to open on the next line.
I changed strInventoryFolder to a dot ".", just because I put the script itself in the same folder as these text files, so the dot just tells it to look int the current folder. If you don't have the script in the same folder, make sure you put in the full path (by UNC should work) to the text files.
Regards,
Rob.
ASKER
yep, put the scrip tin the folder with the text files and it worked.
Thanks very much for all your help
Thanks very much for all your help
No problem. As I mentioned, you can put the script anywhere, just change the strWorkBook and strInventoryFolder values to suit.
Regards,
Rob.
Regards,
Rob.
Mike