multiple excel files in multiple folders

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

multiple excel files in multiple folders

Logged in as: Guest

arrSession:exec spGetSession 2,2,59595

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> WSH & Client Side VBScript >> multiple excel files in multiple folders
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1]

Message

<< Older Topic Newer Topic >>

multiple excel files in multiple folders - 4/28/2008 8:36:18 AM

mycerinus

Posts: 2
Score: 0
Joined: 4/28/2008
Status: offline

First thing,
Really enjoy the site forum and have found some helpful hints.

Here is my issue.

We keep firewall serial numbers in a seperate spreadsheet located in a seperate folder for each customer. The Boss wants a script that will go to each client folder then to the client spreadsheet and pull those cells (up tp 10) from each excel file and put them in either a txt or xls file. The cells are in the same place in each excel file. Cell (i, 52)

I have a script and it "works" in the sense that if I tell it to start in the client folder, it will pull the info from the right cell and put it into a new file. But if I tell it to start in the root of the client folder to look at every client, it starts asking if I want to update the xls files and I tell it no but nothing ever comes across in the newly created xls file. I think it is trying to look through too many files? I want to limit it to just excel files with NCS in the name.

Any pointers or places to start looking would be great.

Thanks,

SC

< Message edited by mycerinus -- 4/28/2008 8:37:35 AM >

Post #: 1

RE: multiple excel files in multiple folders - 4/30/2008 1:49:22 AM

ebgreen

Posts: 4595
Score: 29
Joined: 7/12/2005
Status: offline

Sounds like you have done most of the work and just need a prod in the right direction. Post your code so we can understand exactly what you are doinf and we will give you the prod.

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to mycerinus)

Post #: 2

RE: multiple excel files in multiple folders - 4/30/2008 5:34:10 AM

mycerinus

Posts: 2
Score: 0
Joined: 4/28/2008
Status: offline

The issue "I think" is the 2 lines in red that find all files in all the subfolders with an extention of XLS.
The problem is that there are a lot of excel files in each folder and I only want the ones with "ncs" in the title.
NOTE: "ncs.xls" are ALWAYS the last 7 characters of the file I need.
I tried to do a " Right (ncs.xls, 7) and that did not error out but did not give me data either.

Thanks in advance,
SC

strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colFileList = objWMIService.ExecQuery _
   ("ASSOCIATORS OF {Win32_Directory.Name='s:\support\Client Audits'} Where " _
       & "ResultClass = CIM_DataFile")
For Each objFile In colFileList
   If InStr(objFile.FileName, "FWAudit") Then
       objFile.Delete
   End If
Next
strFolderName = "s:\clients"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set colSubfolders = objWMIService.ExecQuery _
   ("Associators of {Win32_Directory.Name='" & strFolderName & "'} " _
       & "Where AssocClass = Win32_Subdirectory " _
           & "ResultRole = PartComponent")
Set colFiles = objWMIService.ExecQuery _
   ("ASSOCIATORS OF {Win32_Directory.Name='" & strFolderName & "'} Where " _
       & "ResultClass = CIM_DataFile")
For Each objFile in colFiles
      If objFile.Extension = "xls" Then
       Set objExcel = CreateObject("Excel.Application")
       Set objworkbook = objExcel.Workbooks.Open(objFile.Name)
Set objWorksheet = objWorkbook.Worksheets(1)
i = 14
Do While True
   strValue = objWorksheet.Cells(i,52)
   If strValue = "" Then
       Exit Do
   End If
   strText = strText & strValue & vbCrLf
   i = i + 1
Loop

objworkbook.close FALSE
objexcel.quit
   End If
Next
For Each objFolder in colSubfolders
   GetSubFolders strFolderName
Next
Sub GetSubFolders(strFolderName)
   Set colSubfolders2 = objWMIService.ExecQuery _
       ("Associators of {Win32_Directory.Name='" & strFolderName & "'} " _
           & "Where AssocClass = Win32_Subdirectory " _
               & "ResultRole = PartComponent")
   For Each objFolder2 in colSubfolders2
       strFolderName = objFolder2.Name
   Set colFiles = objWMIService.ExecQuery _
       ("ASSOCIATORS OF {Win32_Directory.Name='" & strFolderName & "'} Where " _
           & "ResultClass = CIM_DataFile")
   For Each objFile in colFiles
        If objFile.Extension = "xls" Then
      Set objExcel = CreateObject("Excel.Application")
       Set objWorkbook = objExcel.Workbooks.Open(objFile.Name)
Set objWorksheet = objWorkbook.Worksheets(1)
i = 14
Do While True
   strValue = objWorksheet.Cells(i,52)
   If strValue = "" Then
       Exit Do
   End If
   strText = strText & strValue & vbCrLf
   i = i + 1
Loop
objworkbook.close FALSE
objexcel.quit
end if
Next
       GetSubFolders strFolderName
Next
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.CreateTextFile("s:\support\Client Audits\FWAudit.xls")

objFile.Write strText
objFile.Close

End Sub

(in reply to mycerinus)

Post #: 3

If you found our site useful please link to us <a href="http://www.visualbasicscript.com">VisualBasicScript.com</a>.

All Forums >> [Scripting] >> WSH & Client Side VBScript >> multiple excel files in multiple folders

Page: [1]

Jump to:

New Messages	No New Messages
Hot Topic w/ New Messages	Hot Topic w/o New Messages
Locked w/ New Messages	Locked w/o New Messages

Post New Thread

Reply to Message

Post New Poll

Submit Vote

Delete My Own Post

Delete My Own Thread

Rate Posts