255

[jrew23]

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/viewtopic.php?t=130891&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]

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.)

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/viewtopic.php?t=130891&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...

 

[jrew23]

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/microsoft.public.excel/browse_thread/thread/77270cd75fab6936
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?

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.)

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/viewtopic.php?t=130891&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]

Here's a link to a post that tried to explain how "option explicit" is your
friend:
http://groups.google.co.uk/[email protected]
========
Debra Dalgleish has a nice list of books at:
http://www.contextures.com/xlbooks.html

For VBA:
John Walkenbach's is a nice one to start with. I think that John Green (and
others) is nice, too (for a second book??). See if you can find them in your
local bookstore and you can choose what one you like best.
============

And if you use mrexcel or google, you're actually going through some web
interface to get to the active newsgroups. Google is a very nice archive, but I
find it much easier using a newsreader (like Outlook Express) to read the active
messages (and to post).

Saved from a previous post:

If you have Outlook Express installed, try clicking on these links (or copy and
paste into MSIE).

news://msnews.microsoft.com/microsoft.public.excel.setup
news://msnews.microsoft.com/microsoft.public.excel.misc
news://msnews.microsoft.com/microsoft.public.excel.worksheet.functions
news://msnews.microsoft.com/microsoft.public.excel.newusers
news://msnews.microsoft.com/microsoft.public.excel.programming

(and a few more for MSWord)
news://msnews.microsoft.com/microsoft.public.word.docmanagement
news://msnews.microsoft.com/microsoft.public.word.word97vba
news://msnews.microsoft.com/microsoft.public.word.newusers
news://msnews.microsoft.com/microsoft.public.word.pagelayout
news://msnews.microsoft.com/microsoft.public.word.vba.addins
news://msnews.microsoft.com/microsoft.public.word.vba.beginners
news://msnews.microsoft.com/microsoft.public.word.vba.customization
news://msnews.microsoft.com/microsoft.public.word.vba.general
news://msnews.microsoft.com/microsoft.public.word.vba.userforms
news://msnews.microsoft.com/microsoft.public.word.word6-7macros

(You can always connect to more later)

Here are some links that explain it better:

Chip Pearson has some notes written by Leonard Meads at:
http://www.cpearson.com/excel/DirectConnect.htm

David McRitchie's notes at:
http://www.mvps.org/dmcritchie/excel/xlnews.htm
http://www.mvps.org/dmcritchie/excel/oe6.htm
http://www.mvps.org/dmcritchie/excel/oe6nws01.htm

Tushar Mehta's notes at:
http://www.tushar-mehta.com/misc_tutorials/oe_ng/index.htm

And if you're looking for old posts:

Or you can use google (maybe a few hours behind) to search for stuff you've
posted (and find the replies, too)

http://groups.google.com/advanced_group_search
http://groups.google.com/advanced_group_search?q=group:*Excel*&num=100

Ron de Bruin has an excel addin that you may like:
http://www.rondebruin.nl/Google.htm

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/microsoft.public.excel/browse_thread/thread/77270cd75fab6936
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?

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.)

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/viewtopic.php?t=130891&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...

 

[Tom Ogilvy]

And if you use mrexcel or google, you're actually going through some web

interface to get to the active newsgroups.

I think you are confusing ExcelForum with MrExcel. I don't believe MrExcel
interacts with the newsgroups.

--
Regards,
Tom Ogilvy

Here's a link to a post that tried to explain how "option explicit" is your

friend:

http://groups.google.co.uk/[email protected]
========
Debra Dalgleish has a nice list of books at:
http://www.contextures.com/xlbooks.html

For VBA:
John Walkenbach's is a nice one to start with. I think that John Green (and
others) is nice, too (for a second book??). See if you can find them in your
local bookstore and you can choose what one you like best.
============

And if you use mrexcel or google, you're actually going through some web
interface to get to the active newsgroups. Google is a very nice archive, but I
find it much easier using a newsreader (like Outlook Express) to read the active
messages (and to post).

Saved from a previous post:

If you have Outlook Express installed, try clicking on these links (or copy and
paste into MSIE).

news://msnews.microsoft.com/microsoft.public.excel.setup
news://msnews.microsoft.com/microsoft.public.excel.misc
news://msnews.microsoft.com/microsoft.public.excel.worksheet.functions
news://msnews.microsoft.com/microsoft.public.excel.newusers
news://msnews.microsoft.com/microsoft.public.excel.programming

(and a few more for MSWord)
news://msnews.microsoft.com/microsoft.public.word.docmanagement
news://msnews.microsoft.com/microsoft.public.word.word97vba
news://msnews.microsoft.com/microsoft.public.word.newusers
news://msnews.microsoft.com/microsoft.public.word.pagelayout
news://msnews.microsoft.com/microsoft.public.word.vba.addins
news://msnews.microsoft.com/microsoft.public.word.vba.beginners
news://msnews.microsoft.com/microsoft.public.word.vba.customization
news://msnews.microsoft.com/microsoft.public.word.vba.general
news://msnews.microsoft.com/microsoft.public.word.vba.userforms
news://msnews.microsoft.com/microsoft.public.word.word6-7macros

(You can always connect to more later)

Here are some links that explain it better:

Chip Pearson has some notes written by Leonard Meads at:
http://www.cpearson.com/excel/DirectConnect.htm

David McRitchie's notes at:
http://www.mvps.org/dmcritchie/excel/xlnews.htm
http://www.mvps.org/dmcritchie/excel/oe6.htm
http://www.mvps.org/dmcritchie/excel/oe6nws01.htm

Tushar Mehta's notes at:
http://www.tushar-mehta.com/misc_tutorials/oe_ng/index.htm

And if you're looking for old posts:

Or you can use google (maybe a few hours behind) to search for stuff you've
posted (and find the replies, too)

http://groups.google.com/advanced_group_search
http://groups.google.com/advanced_group_search?q=group:*Excel*&num=100

Ron de Bruin has an excel addin that you may like:
http://www.rondebruin.nl/Google.htm

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/microsoft.public.excel/browse_thread/thread/77270cd75fab6936
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?

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.)




(e-mail address removed) 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/viewtopic.php?t=130891&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]

I don't recall every visiting MrExcel, so that's entirely possible.

Thanks for the correction.

And if you use mrexcel or google, you're actually going through some web
interface to get to the active newsgroups.

I think you are confusing ExcelForum with MrExcel. I don't believe MrExcel
interacts with the newsgroups.

--
Regards,
Tom Ogilvy

Here's a link to a post that tried to explain how "option explicit" is your

friend:

http://groups.google.co.uk/[email protected]
========
Debra Dalgleish has a nice list of books at:
http://www.contextures.com/xlbooks.html

For VBA:
John Walkenbach's is a nice one to start with. I think that John Green (and
others) is nice, too (for a second book??). See if you can find them in your
local bookstore and you can choose what one you like best.
============

And if you use mrexcel or google, you're actually going through some web
interface to get to the active newsgroups. Google is a very nice archive, but I
find it much easier using a newsreader (like Outlook Express) to read the active
messages (and to post).

Saved from a previous post:

If you have Outlook Express installed, try clicking on these links (or copy and
paste into MSIE).

news://msnews.microsoft.com/microsoft.public.excel.setup
news://msnews.microsoft.com/microsoft.public.excel.misc
news://msnews.microsoft.com/microsoft.public.excel.worksheet.functions
news://msnews.microsoft.com/microsoft.public.excel.newusers
news://msnews.microsoft.com/microsoft.public.excel.programming

(and a few more for MSWord)
news://msnews.microsoft.com/microsoft.public.word.docmanagement
news://msnews.microsoft.com/microsoft.public.word.word97vba
news://msnews.microsoft.com/microsoft.public.word.newusers
news://msnews.microsoft.com/microsoft.public.word.pagelayout
news://msnews.microsoft.com/microsoft.public.word.vba.addins
news://msnews.microsoft.com/microsoft.public.word.vba.beginners
news://msnews.microsoft.com/microsoft.public.word.vba.customization
news://msnews.microsoft.com/microsoft.public.word.vba.general
news://msnews.microsoft.com/microsoft.public.word.vba.userforms
news://msnews.microsoft.com/microsoft.public.word.word6-7macros

(You can always connect to more later)

Here are some links that explain it better:

Chip Pearson has some notes written by Leonard Meads at:
http://www.cpearson.com/excel/DirectConnect.htm

David McRitchie's notes at:
http://www.mvps.org/dmcritchie/excel/xlnews.htm
http://www.mvps.org/dmcritchie/excel/oe6.htm
http://www.mvps.org/dmcritchie/excel/oe6nws01.htm

Tushar Mehta's notes at:
http://www.tushar-mehta.com/misc_tutorials/oe_ng/index.htm

And if you're looking for old posts:

Or you can use google (maybe a few hours behind) to search for stuff you've
posted (and find the replies, too)

http://groups.google.com/advanced_group_search
http://groups.google.com/advanced_group_search?q=group:*Excel*&num=100

Ron de Bruin has an excel addin that you may like:
http://www.rondebruin.nl/Google.htm

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/microsoft.public.excel/browse_thread/thread/77270cd75fab6936
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.)




(e-mail address removed) 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/viewtopic.php?t=130891&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...

 

[Tom Ogilvy]

No, Mr Excel doesn't provide access to the newsgroups I don't believe.

--
Regards,
Tom Ogilvy

I don't recall every visiting MrExcel, so that's entirely possible.

Thanks for the correction.

And if you use mrexcel or google, you're actually going through some web
interface to get to the active newsgroups.

I think you are confusing ExcelForum with MrExcel. I don't believe MrExcel
interacts with the newsgroups.

--
Regards,
Tom Ogilvy

Here's a link to a post that tried to explain how "option explicit" is your
friend:

http://groups.google.co.uk/[email protected]

========
Debra Dalgleish has a nice list of books at:
http://www.contextures.com/xlbooks.html

For VBA:
John Walkenbach's is a nice one to start with. I think that John

Green
(and

others) is nice, too (for a second book??). See if you can find them

in
your

local bookstore and you can choose what one you like best.
============

And if you use mrexcel or google, you're actually going through some web
interface to get to the active newsgroups. Google is a very nice

archive,
but I

find it much easier using a newsreader (like Outlook Express) to read

the
active

messages (and to post).

Saved from a previous post:

If you have Outlook Express installed, try clicking on these links (or copy and
paste into MSIE).

news://msnews.microsoft.com/microsoft.public.excel.setup
news://msnews.microsoft.com/microsoft.public.excel.misc
news://msnews.microsoft.com/microsoft.public.excel.worksheet.functions
news://msnews.microsoft.com/microsoft.public.excel.newusers
news://msnews.microsoft.com/microsoft.public.excel.programming

(and a few more for MSWord)
news://msnews.microsoft.com/microsoft.public.word.docmanagement
news://msnews.microsoft.com/microsoft.public.word.word97vba
news://msnews.microsoft.com/microsoft.public.word.newusers
news://msnews.microsoft.com/microsoft.public.word.pagelayout
news://msnews.microsoft.com/microsoft.public.word.vba.addins
news://msnews.microsoft.com/microsoft.public.word.vba.beginners
news://msnews.microsoft.com/microsoft.public.word.vba.customization
news://msnews.microsoft.com/microsoft.public.word.vba.general
news://msnews.microsoft.com/microsoft.public.word.vba.userforms
news://msnews.microsoft.com/microsoft.public.word.word6-7macros

(You can always connect to more later)

Here are some links that explain it better:

Chip Pearson has some notes written by Leonard Meads at:
http://www.cpearson.com/excel/DirectConnect.htm

David McRitchie's notes at:
http://www.mvps.org/dmcritchie/excel/xlnews.htm
http://www.mvps.org/dmcritchie/excel/oe6.htm
http://www.mvps.org/dmcritchie/excel/oe6nws01.htm

Tushar Mehta's notes at:
http://www.tushar-mehta.com/misc_tutorials/oe_ng/index.htm

And if you're looking for old posts:

Or you can use google (maybe a few hours behind) to search for stuff you've
posted (and find the replies, too)

http://groups.google.com/advanced_group_search
http://groups.google.com/advanced_group_search?q=group:*Excel*&num=100

Ron de Bruin has an excel addin that you may like:
http://www.rondebruin.nl/Google.htm



(e-mail address removed) wrote:

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/microsoft.public.excel/browse_thread/thread/77270cd75fab6936

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.)




(e-mail address removed) 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/viewtopic.php?t=130891&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]

I was trying to agree with you <bg>.

(So that's entirely possible <that you are correct>.)

No, Mr Excel doesn't provide access to the newsgroups I don't believe.

--
Regards,
Tom Ogilvy

I don't recall every visiting MrExcel, so that's entirely possible.

Thanks for the correction.

And if you use mrexcel or google, you're actually going through some web
interface to get to the active newsgroups.

I think you are confusing ExcelForum with MrExcel. I don't believe MrExcel
interacts with the newsgroups.

--
Regards,
Tom Ogilvy

Here's a link to a post that tried to explain how "option explicit" is
your
friend:

http://groups.google.co.uk/[email protected]
========
Debra Dalgleish has a nice list of books at:
http://www.contextures.com/xlbooks.html

For VBA:
John Walkenbach's is a nice one to start with. I think that John Green
(and
others) is nice, too (for a second book??). See if you can find them in
your
local bookstore and you can choose what one you like best.
============

And if you use mrexcel or google, you're actually going through some web
interface to get to the active newsgroups. Google is a very nice archive,
but I
find it much easier using a newsreader (like Outlook Express) to read the
active
messages (and to post).

Saved from a previous post:

If you have Outlook Express installed, try clicking on these links (or
copy and
paste into MSIE).

news://msnews.microsoft.com/microsoft.public.excel.setup
news://msnews.microsoft.com/microsoft.public.excel.misc
news://msnews.microsoft.com/microsoft.public.excel.worksheet.functions
news://msnews.microsoft.com/microsoft.public.excel.newusers
news://msnews.microsoft.com/microsoft.public.excel.programming

(and a few more for MSWord)
news://msnews.microsoft.com/microsoft.public.word.docmanagement
news://msnews.microsoft.com/microsoft.public.word.word97vba
news://msnews.microsoft.com/microsoft.public.word.newusers
news://msnews.microsoft.com/microsoft.public.word.pagelayout
news://msnews.microsoft.com/microsoft.public.word.vba.addins
news://msnews.microsoft.com/microsoft.public.word.vba.beginners
news://msnews.microsoft.com/microsoft.public.word.vba.customization
news://msnews.microsoft.com/microsoft.public.word.vba.general
news://msnews.microsoft.com/microsoft.public.word.vba.userforms
news://msnews.microsoft.com/microsoft.public.word.word6-7macros

(You can always connect to more later)

Here are some links that explain it better:

Chip Pearson has some notes written by Leonard Meads at:
http://www.cpearson.com/excel/DirectConnect.htm

David McRitchie's notes at:
http://www.mvps.org/dmcritchie/excel/xlnews.htm
http://www.mvps.org/dmcritchie/excel/oe6.htm
http://www.mvps.org/dmcritchie/excel/oe6nws01.htm

Tushar Mehta's notes at:
http://www.tushar-mehta.com/misc_tutorials/oe_ng/index.htm

And if you're looking for old posts:

Or you can use google (maybe a few hours behind) to search for stuff
you've
posted (and find the replies, too)

http://groups.google.com/advanced_group_search
http://groups.google.com/advanced_group_search?q=group:*Excel*&num=100

Ron de Bruin has an excel addin that you may like:
http://www.rondebruin.nl/Google.htm



(e-mail address removed) wrote:

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/microsoft.public.excel/browse_thread/thread/77270cd75fab6936
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.)




(e-mail address removed) 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/viewtopic.php?t=130891&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...

 

[jroo]

Dave - I ran this code today and it worked nicely. Thanks so much!

 

[Dave Peterson]

EXCELlent!

Dave - I ran this code today and it worked nicely. Thanks so much!