Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ERROR: subscript out of range
Hi All, I am getting subscript out of range error on this line:
---------------Set wbk = Workbooks("C:\Documents\WorkBook1") here is my code Sub Submit_Click() Dim wbk As Workbook Dim ws As Worksheet Dim FName As String Set wbk = Workbooks("C:\Documents\WorkBook1") With Worksheets("Sheet1") Range("A1").Value = Me.Name.Value End With FName = "C:\Documents\" _ & "File_of" & "-" & Me.Name.Value & ".xls" Application.DisplayAlerts = False With wbk ..SaveAs Filename:=FName ..Close End With end sub Thanks in advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ERROR: subscript out of range
Something doesn't look right. Maybe this:
Set wbk = Workbooks("C:\Documents\WorkBook1\") HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "sam" wrote: Hi All, I am getting subscript out of range error on this line: ---------------Set wbk = Workbooks("C:\Documents\WorkBook1") here is my code Sub Submit_Click() Dim wbk As Workbook Dim ws As Worksheet Dim FName As String Set wbk = Workbooks("C:\Documents\WorkBook1") With Worksheets("Sheet1") Range("A1").Value = Me.Name.Value End With FName = "C:\Documents\" _ & "File_of" & "-" & Me.Name.Value & ".xls" Application.DisplayAlerts = False With wbk .SaveAs Filename:=FName .Close End With end sub Thanks in advance |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ERROR: subscript out of range
Getting a work book this way will only work if the workbook is already open.
Try: Set wbk = Workbooks("WorkBook1") or Set wbk = Workbooks("WorkBook1.xls") -- Gary''s Student - gsnu200907 "sam" wrote: Hi All, I am getting subscript out of range error on this line: ---------------Set wbk = Workbooks("C:\Documents\WorkBook1") here is my code Sub Submit_Click() Dim wbk As Workbook Dim ws As Worksheet Dim FName As String Set wbk = Workbooks("C:\Documents\WorkBook1") With Worksheets("Sheet1") Range("A1").Value = Me.Name.Value End With FName = "C:\Documents\" _ & "File_of" & "-" & Me.Name.Value & ".xls" Application.DisplayAlerts = False With wbk .SaveAs Filename:=FName .Close End With end sub Thanks in advance |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ERROR: subscript out of range
'If the workbook is not open
Set wbk = Workbooks.Open("C:\geo13.xls") 'If the workbook is already open (mention only the xls name..If not saved it would be like book1, book2 etc; withoutt the extension) Set wbk = Workbooks("geo13.xls") If this post helps click Yes --------------- Jacob Skaria "sam" wrote: Hi All, I am getting subscript out of range error on this line: ---------------Set wbk = Workbooks("C:\Documents\WorkBook1") here is my code Sub Submit_Click() Dim wbk As Workbook Dim ws As Worksheet Dim FName As String Set wbk = Workbooks("C:\Documents\WorkBook1") With Worksheets("Sheet1") Range("A1").Value = Me.Name.Value End With FName = "C:\Documents\" _ & "File_of" & "-" & Me.Name.Value & ".xls" Application.DisplayAlerts = False With wbk .SaveAs Filename:=FName .Close End With end sub Thanks in advance |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ERROR: subscript out of range
To use this line:
Set wbk = Workbooks("C:\Documents\WorkBook1") The workbook has to be open already. And you don't include the drive or path. This may fail: Set wbk = Workbooks("WorkBook1") This will always work: Set wbk = Workbooks("WorkBook1.xls") sam wrote: Hi All, I am getting subscript out of range error on this line: ---------------Set wbk = Workbooks("C:\Documents\WorkBook1") here is my code Sub Submit_Click() Dim wbk As Workbook Dim ws As Worksheet Dim FName As String Set wbk = Workbooks("C:\Documents\WorkBook1") With Worksheets("Sheet1") Range("A1").Value = Me.Name.Value End With FName = "C:\Documents\" _ & "File_of" & "-" & Me.Name.Value & ".xls" Application.DisplayAlerts = False With wbk .SaveAs Filename:=FName .Close End With end sub Thanks in advance -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
ERROR: subscript out of range
Hey Dave, Thanks for the help.
I still get a Subscript out of range error if i use: Set wbk = Workbooks("WorkBook1.xls") But this works fine: Set wbk = Workbooks.open("C:\Documents\WorkBook1.xls") should it be this way? Thanks in advance "Dave Peterson" wrote: To use this line: Set wbk = Workbooks("C:\Documents\WorkBook1") The workbook has to be open already. And you don't include the drive or path. This may fail: Set wbk = Workbooks("WorkBook1") This will always work: Set wbk = Workbooks("WorkBook1.xls") sam wrote: Hi All, I am getting subscript out of range error on this line: ---------------Set wbk = Workbooks("C:\Documents\WorkBook1") here is my code Sub Submit_Click() Dim wbk As Workbook Dim ws As Worksheet Dim FName As String Set wbk = Workbooks("C:\Documents\WorkBook1") With Worksheets("Sheet1") Range("A1").Value = Me.Name.Value End With FName = "C:\Documents\" _ & "File_of" & "-" & Me.Name.Value & ".xls" Application.DisplayAlerts = False With wbk .SaveAs Filename:=FName .Close End With end sub Thanks in advance -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
ERROR: subscript out of range
If there is no open workbook named workbook1.xls, then this will fail:
set wbk = workbooks("workbook1.xls") If there is a workbook with that name that's already open (by the user???), then it will work ok. If you don't have a workbook with that name open and you want to open it, then you'd use that second version. It really depends on where you're starting. If you (as the developer) don't know if the user already opened that workbook--but your program needs it, you could combine them... Dim wkbk as workbook dim wkbkName as string dim wkbkPath as string wkbkname = "workbook1.xls" wkbkpath = "C:\documents\" '<-- include that trailing backslash! set wkbk = nothing on error resume next set wkbk = workbooks(wkbkname) on error goto 0 if wkbk is nothing then 'it wasn't set correctly, so it's not open. 'so try to open it! on error resume next set wkbk = workbooks.open(filename:=wkbkpath & wkbkname) on error goto 0 end if if wkbk is nothing then msgbox wkbkname & " wasn't opened and couldn't be found in " & wkbkpath exit sub '???? end if msgbox "It's ready to be used! sam wrote: Hey Dave, Thanks for the help. I still get a Subscript out of range error if i use: Set wbk = Workbooks("WorkBook1.xls") But this works fine: Set wbk = Workbooks.open("C:\Documents\WorkBook1.xls") should it be this way? Thanks in advance "Dave Peterson" wrote: To use this line: Set wbk = Workbooks("C:\Documents\WorkBook1") The workbook has to be open already. And you don't include the drive or path. This may fail: Set wbk = Workbooks("WorkBook1") This will always work: Set wbk = Workbooks("WorkBook1.xls") sam wrote: Hi All, I am getting subscript out of range error on this line: ---------------Set wbk = Workbooks("C:\Documents\WorkBook1") here is my code Sub Submit_Click() Dim wbk As Workbook Dim ws As Worksheet Dim FName As String Set wbk = Workbooks("C:\Documents\WorkBook1") With Worksheets("Sheet1") Range("A1").Value = Me.Name.Value End With FName = "C:\Documents\" _ & "File_of" & "-" & Me.Name.Value & ".xls" Application.DisplayAlerts = False With wbk .SaveAs Filename:=FName .Close End With end sub Thanks in advance -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subscript Out of Range Error | Excel Programming | |||
Runtime Error - Subscript out of range despite On Error statement | Excel Programming | |||
Subscript out of range error - save copy error | Excel Programming | |||
Subscript out of range error - save copy error | Excel Programming | |||
Type Mismatch error & subscript out of range error | Excel Programming |