Fix a macro ( or spot the dumb mistake I made)


Posted by SWallace on December 13, 2001 8:10 AM

Posted by Swallace on December 13, 2001 8:14 AM

Doh !!!
this macro (culled from this bulletin board )worked yesterday at opening files but today (when I
added the code to move the selection) so I don't paste over the data I have linked to from the first file
It wont open any files !! It says the file can't be found.
If it was opening the first file and then bombing I'd understand but ... here's the code.

Dim File() As String
Dim FoundFile As String
Dim FileCount As Integer


Sub ConvertMathCadData()
FoundFile = Dir("C:\calc\*.xls")

FileCount = 1
ReDim Preserve File(FileCount)
File(FileCount) = FoundFile

Do While FoundFile <> ""
FoundFile = Dir()
If FoundFile <> "" Then
FileCount = FileCount + 1
ReDim Preserve File(FileCount)
File(FileCount) = FoundFile
End If
Loop

For i = 1 To FileCount
Workbooks.Open File(i)

Sheets("totals").Select
Range("B14:B16").Select
Selection.Copy
Windows("Calculator.xls").Activate
ActiveSheet.Paste Link:=True
Selection.Offset(0, 1).Select
Next i

End Sub


Lollipop for the one who fixes it !!

Thanks



Posted by SWallace on December 13, 2001 8:35 AM

Fixed It myself ( starting to get the hang of this now ) needed to use ChDir to set the correct path for the file Cheers for the help anyway !!