Help with VB Code to find an Excel Spreadsheet.

[hgraybill]

There's a program I'm trying to modify (VB 6) that contains a section of code that looks for an Excel file (according to a file name snippet in another program). At this point it will only go out and look for one file and add that total to a report, but I would like it to go out and look for more than one file (the file name snippets would be separated by a semicolon) and get the totals for all the Excel files and add them together.

Here is the code section is question. Any help is appreciated. I haven't had ANY luck.

''GET EXCEL FEASIBILITY STUDY DATA
FileNameStart = MDIParent.Adodc1.Recordset.Fields("USER_4")
If FileNameStart = "" Or IsNull(FileNameStart)
Then GoTo AddRec
EST_MAT_COST = 0
EST_LAB_COST = 0
FileName = ""

''CHECK FILE NAME
For I = 0 To File1.ListCount - 1
File1.ListIndex = I
If UCase(Left(File1.FileName, Len(FileNameStart))) = UCase(FileNameStart)
Then FileName = File1.FileName
Exit For
End If
Next I

If FileName <> ""
Then xlFile.Workbooks.Open ExcelPath & "\" & FileName

Dim xlSheet As Excel.Worksheet
Set xlSheet = xlFile.Sheets("NRE")

''SEARCH FOR ''TOTAL''
CONTINUE = True
ROW_COUNT = 1
Do While CONTINUE
If xlSheet.Cells(ROW_COUNT, 1) = "TOTAL" Then
Exit Do
End If
ROW_COUNT = ROW_COUNT + 1
Loop
EST_MAT_COST = xlSheet.Cells(ROW_COUNT, 5) + xlSheet.Cells(ROW_COUNT, 6) + xlSheet.Cells(ROW_COUNT, 7)
EST_LAB_COST = xlSheet.Cells(ROW_COUNT, 4)

xlFile.ActiveWorkbook.Close False
xlFile.Quit

End If

 

[HarleyQuinn]

How about splitting the name snippets into an array of filenames, and then looping through that array executing the code each time. You can hold running totals while you go along, something like:

Dim i as Integer
Dim arrFileNames() as String

arrFileNames = Split(YourFilenamesString,";")

For i = 0 to Ubound(arrFileNames)

  Your code but using arrFileNames(i) as the filename...
  Add to running totals (you can do this however you fancy)
Next i

Output running totals...

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[hgraybill]

Thank you very much! That will probably work...I'll give it a try today.

Thanks again,

Heather

 

[HarleyQuinn]

You're welcome, hope it works for you

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[hgraybill]

It works great, except now I can't get it to trim the file name and still find it. Before I had them putting a shortened version of the file name in and the program still knew how to translate it, but I can't fit the code in to trim it again. This code only accepts the whole file name.

Sorry for all the questions. I'm still new to programming.

Thanks,
Heather

 

[HarleyQuinn]

No worries about the questions, that's what the site is for

I'm assuming that the code under ''CHECK FILE NAME is where you get the full name from. I would check that the fullname is in File1, if it is, I can't see a reason why it wouldn't pick it up just as it was before.

If this doesn't work could you post your code and I/we'll have a look.

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[hgraybill]

I was able to figure it out in the end and it works.

Thanks for all your help!

Heather