Dave, Thanks for the quick reply... I'll try running your code from
work on Monday. For the meantime... i'd like to learn more about the
fundamentals of excel vba programming. I'm not much of a programmer.
For instance, I'm not even sure what "Option Explicit" does. Do you
have any suggestions on sites or books that I should read? I learn
better from seeing examples. I have a hard time trying to code from
scratch.
Also, I saw your note on public.excel
http://groups-beta.google.com/group/...270cd75fab6936
Let me explain. At first, I posted my question on the Mr Excel Message
Board a few days ago, and someone helped me. But I came across other
issues and didn't get a response on those. Then this morning I
"disovered" the google forums.. so i figured i try it out, posting it
here (excel.progamming) but I accidentally forgot to include a better
descriptive subject line. I noticed my mistake right away, but I
couldn't figure how to re-edit the subject line once it was posted. I
was just concerned that most people would over look my post because it
had a vague subject. So i thought it wouldn't hurt to post a reference
on public.excel because (1) I wasn't sure which forum was "better" and
(2) to include a better Subject Line other than "255".
You had said it's unusual to post to multiple forums... did you mean by
posting it at two forums within Google. Or Google and Mr. Excel?
Dave Peterson wrote:
I think you were hit with excel's limit of returning 255 characters
from a
closed workbook.
If you have to keep the formulas, then as soon as these formulas
recalculate
(just recalculate--not rerunning the macro), then the results of the
formula are
gonna get truncated again.
One option is to open the workbook in your sub (essentially the
suggestion you
got), retrieve the value in code and plop that returned value into
the
worksheet.
But the bad news is that if you don't want truncation, you have to
open up those
workbooks. That could make the routine slow.
Option Explicit
Sub Option3()
Dim myCount As Long
Dim iRow As Long
Dim myFileName As String
Dim myWorksheetName As String
Dim myFolder As String
Dim testStr As String
Dim res As Variant
Dim myValue As Variant
Dim tempWkbk As Workbook
Dim testWks As Worksheet
myFolder = "C:\My Documents\Survey\"
myWorksheetName = "Explanations"
With ActiveSheet
myCount = .Range(.Range("A5"),
..Range("A5").End(xlDown)).Count + 4
For iRow = 5 To myCount
myFileName = myFolder & .Cells(iRow, 1) & ".xls"
testStr = ""
On Error Resume Next
testStr = Dir(myFileName)
On Error GoTo 0
If testStr = "" Then
'file not found
myValue = "File Not found"
Else
Set tempWkbk = Workbooks.Open(Filename:=myFileName, _
ReadOnly:=True)
Set testWks = Nothing
On Error Resume Next
Set testWks = tempWkbk.Worksheets(myWorksheetName)
On Error GoTo 0
If testWks Is Nothing Then
myValue = "Worksheet not found"
Else
res = Application.VLookup(.Cells(iRow, 3).Value,
_
testWks.Range("A1:b100"), 2, False)
If IsError(res) Then
myValue = "Missing from Table"
Else
myValue = res
End If
End If
tempWkbk.Close savechanges:=False
End If
.Cells(iRow, 15).Value = myValue
Next iRow
End With
End Sub
(I wouldn't have a guess why you got the 1004 error without knowing
what line
caused the error.)
wrote:
Hello,
This is my first posting on this forum. I first posted my question
on
the Mr Excel Message Board -
http://www.mrexcel.com/board2/viewto...891&highlight=
... I got some help, but i'm still having some problems.
Here's what I need to do:
I have a worksheet that lists 424 records displaying "file names"
(in
column A) and "line numbers" (in column C). I'm using the vlookup
funciton in my macro to retrieve explanations from files in another
folder.
'-----------------------------------------------
Sub Option1()
Dim MyCount
MyCount = Range(Range("A5"), Range("A5").End(xlDown)).count + 4
For i = 5 To MyCount
Cells(i, 15).Formula = "=VLOOKUP(C" & i & ",'C:\My
Documents\Survey\[" & Cells(i, 1) & _
".xls]Explanations'!$A$1:$B$100,2,FALSE)"
Next i
End Sub
'-----------------------------------------------
This works reasonably well (and fast), with the exception that if
an
explanation was longer than 255 characters it gets truncated. A
person
on Mr Excel responded and suggested that I use the following code
instead:
'-----------------------------------------------
Sub Option2()
Application.ScreenUpdating = False
Dim c As Range
Dim StrToFind As String
Dim MyCount
MyCount = Range(Range("A5"), Range("A5").End(xlDown)).count + 4
'On Error GoTo ErrorOccurred
For i = 5 To MyCount
ThisWorkbook.Activate
Sheets(1).Activate
StrToFind = Cells(i, "C")
Workbooks.Open Filename:= _
"C:\My Documents\Survey\" & Cells(i, 1) & ".xls"
Sheets("Explanations").Select
Set c = Range("A1:A100").Find(What:=StrToFind,
After:=Range("A1"),
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False)
If Not c Is Nothing Then
ThisWorkbook.ActiveSheet.Cells(i, 15) = c.Offset(0, 1)
Else
ThisWorkbook.ActiveSheet.Cells(i, 15) = "Not found"
End If
ActiveWorkbook.Close (False)
Next i
Exit Sub
ErrorOccurred:
ThisWorkbook.ActiveSheet.Cells(i, 15) = "ERROR"
Resume Next
Application.ScreenUpdating = False
End Sub
'-----------------------------------------
I ran this macro for the sample of 20 records that had more than
255
characters and only one resulted in having an error
"Run-time error '1004': Application-defined or object-defined
error"
(I found this record had 1,104 characters in the explanation)
What does this error mean?
In addition, when i ran the macro for all 424 records, it took
extremely long. After 15 minutes I hit escape and found that only
100
observations were processed.
Does anyone have any ideas to solve my problem? Can code be added
to
either my option1 or option2 macros to have this run more
efficiently... or should i start from scratch? Please help!
THANKS...
--
Dave Peterson