run-time error '9' Subscript out of range

[Jeffrey.Tiong]

Hi,

I was trying to write a script that import all the .dat files into one
excel workbook. Each .dat file will become a new sheet in the workbook.
After importing, then i want to plot a graph for the 2 of the columns
of every newly imported dat file. The macro script below works fine for
importing multiple dat files into the workbook. But when i add the part
on plotting the graph, it came out error "run time error '9' Subscript
out of range". What is the problem and how to make it work? Thanks!!!

Jef


Option Explicit
Sub testme01()

Dim newWkbk As Workbook
Dim newWks As Worksheet
Dim tempwks As Worksheet
Dim myFileNames As Variant
Dim fCtr As Long
Dim myworkbooks As Worksheet

Set newWkbk = Workbooks.Add


Set newWks = Workbooks.Add(1).Worksheets(1)



myFileNames = Application.GetOpenFilename _
(FileFilter:="dat Files, *.dat", _
MultiSelect:=True)

Application.ScreenUpdating = False

If IsArray(myFileNames) Then
For fCtr = LBound(myFileNames) To UBound(myFileNames)

Workbooks.OpenText Filename:=myFileNames(fCtr), _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=False, Space:=False, Other:=False



Set tempwks = ActiveSheet

Range("A1:B4").Select
Selection.Cut
Range("G5").Select
ActiveSheet.Paste
Range("B:B,D").Select
Range("D1").Activate

tempwks.Copy _
before:=newWks
tempwks.Parent.Close savechanges:=False


Charts.Add
ActiveChart.ChartType = xlXYScatterSmooth
ActiveChart.SetSourceData
Source:=Sheets("tempwks").Range("B:B,D") 'error here
ActiveChart.Location Where:=xlLocationAsObject, Name:="tempwks"


Next fCtr
Application.DisplayAlerts = False
newWks.Delete
Application.DisplayAlerts = True
Else
MsgBox "Ok, Quitting"
End If

Application.ScreenUpdating = True
End Sub

 

[Cindy M.]

I was trying to write a script that import all the .dat files into one

excel workbook. Each .dat file will become a new sheet in the workbook.
After importing, then i want to plot a graph for the 2 of the columns
of every newly imported dat file. The macro script below works fine for
importing multiple dat files into the workbook. But when i add the part
on plotting the graph, it came out error "run time error '9' Subscript
out of range". What is the problem and how to make it work?

If you show us which line of code is causing the error, we might be able
to help you...

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[Jeffrey.Tiong]

Hi Cindy,

The error comes from this row....

Source:=Sheets("tempwks").Range("B:B,D")

Thanks!!!!!!!