Script out of range error

[leal72]

Having a problem with this part of my code. When I test it on my PC it works fine but the PC of the end user, he gets an error "Script out of range"

[VBA]
' User Input box to name workbook
WkbName = InputBox("Name the workbook to store test data", "WORKBOOK NAME")

' Create new workbook with single worksheet
Set NewWkb = Workbooks.Add(xlWBATWorksheet)

' Exit if dialog box canceled
If WkbName <> "" Then
NewWkb.SaveAs FileName:=WkbName ' Save workbook with InputBox info
Else
NewWkb.Close ' Close created workbook if InputBox is cancelled
Exit Sub
End If

' Rename Sheet1
Workbooks(WkbName).Sheets(1).Name = "SummaryData" <<<script out of range error here
' Select Raw Data (*.csv) files
Files = Application.GetOpenFilename _
("Text Files (*.txt), *.txt", _
Title:="Select which data files to import", MultiSelect:=True)

' Exit macro if no files were selected
If Not IsArray(Files) Then
MsgBox "No file was selected."
Exit Sub
End If

' Loop through selected files and add to Results workbook
For z = LBound(Files) To UBound(Files)

Workbooks.OpenText FileName:=Files(z), _
Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True

' store the workbook name in variable "temp"
WsName = ActiveSheet.Name
' Moves active sheet to named workbook
Sheets(WsName).Move after:=Workbooks(WkbName).Sheets("SummaryData")
[/VBA]

[Tinbendr]

Can't remember why that doesn't work, but NewWkb.Sheets(1).Name does.

[Bob Phillips]

Maybe because there is no .xls at the end of the string.

Try this

[vba]

If WkbName <> "" Then
NewWkb.SaveAs FileName:=WkbName ' Save workbook with InputBox info
WkbName = NewWkb.Name
Else
NewWkb.Close ' Close created workbook if InputBox is cancelled
Exit Sub
End If
[/vba]

[leal72]

thank you, I'll try these

[GTO]

Having a problem with this part of my code. When I test it on my PC it works fine but the PC of the end user, he gets an error "Script out of range"...

Greetings leal72,

Hope you won't mind the intrusion, but an answer to this might help us both?

Say fellas,

I know I should remember this, but am drawing a blank:

If leal has the "Hide extensions for known file types" checkbox ticked and the other/end user does not, then leaving out the file extension seems to work in the first case, but of course not in the second.

Convserely though, if the file extension is included, it seems to work in either case.

Why?

Mark

[leal72]

Greetings leal72,

Hope you won't mind the intrusion, but an answer to this might help us both?

Say fellas,

I know I should remember this, but am drawing a blank:

If leal has the "Hide extensions for known file types" checkbox ticked and the other/end user does not, then leaving out the file extension seems to work in the first case, but of course not in the second.

Convserely though, if the file extension is included, it seems to work in either case.

Why?

Mark

I wasn't able to find this option but it seems to explain part of the problem

the suggetion to update the text string using ".name" did the trick

thank you

[GTO]

Sorry about that, I was a bit obscure. As you know, both xld's and tinbendr's suggestions would fix the issue, I was being curious/nosy as to the why.

In case you want file extensions to display, open Windows Explorer, and from the menu bar, I believe its Tools | Options. From there I believe its the second tab (unfortunately, at work right now, and this is blocked so I cannot check) and you'll see a checkbox with wording similar to what I had above. If you un-tick this and apply, file extensions then display.

I did not find any info as to the 'why' this causes a problem one way but not the other, but did find this:

http://www.cpearson.com/excel/FileExtensions.aspx

Mark

[leal72]

I'll take a look at that, thank you.