Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
run time error 9
Hello all A while back I had to quickly put together a routine to take the information from a main workbook and copy and save aspects of the data into many different workbooks using a second workbook as a template. At the time I recorded a macro to deal with the repetitive stuff which meant that the workbook names of the workbook containing the data and the template where embedded in the code. I am trying to tidy it up by using the code below to specify the main workbook and the template. Sub GetFilePathCopyTo() Dim Finfo As String Dim FilterIndex As Integer Dim Title As String ' Set up list of file filters Finfo = "Text Files (*.txt),*.txt," & _ "Lotus Files (*.prn),*.prn," & _ "Comma Separated Files (*.csv),*.csv," & _ "ASCII Files (*.asc),*.asc," & _ "All Files (*.*),*.*" ' Display *.* by default FilterIndex = 5 ' Set the dialog box caption Title = "Select a File to Copy To" ' Get the filename FilePathCopyTo = Application.GetOpenFilename(Finfo, _ FilterIndex, Title) ' Handle return info from dialog box If FilePathCopyTo = False Then MsgBox "No file was selected." Else MsgBox "You selected " & FilePathCopyTo End If End Sub This works fine and I changed the next bit of code in the process from Sub OpenRequote() Workbooks.Open Filename:= _ "I:\Costings\Development\2005 - 2006\Requote\requote template.xls" Range("B6").Select Windows("SAStanCosts WE 11 19 05.xls").Activate End Sub To this so that the hard coded workbook name was replaced Sub OpenRequote() Workbooks.Open FileName:= FilePathCopyTo Range("B6").Select Windows(FilePathCopyTo).Activate End Sub I am getting a run time error 9 when the code gets to Windows(FilePathCopyTo).Activate and I don’t know what to do, can anybody help? Regards Adrian -- SandyUK ------------------------------------------------------------------------ SandyUK's Profile: http://www.excelforum.com/member.php...o&userid=17487 View this thread: http://www.excelforum.com/showthread...hreadid=547362 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
run time error 9
Sandy,
Have you declared FilePathToCopyTo as a module level variable? Otherwise, it will pick up a procedure level variable in each procedure, a different one each time, and it may be blank. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "SandyUK" wrote in message ... Hello all A while back I had to quickly put together a routine to take the information from a main workbook and copy and save aspects of the data into many different workbooks using a second workbook as a template. At the time I recorded a macro to deal with the repetitive stuff which meant that the workbook names of the workbook containing the data and the template where embedded in the code. I am trying to tidy it up by using the code below to specify the main workbook and the template. Sub GetFilePathCopyTo() Dim Finfo As String Dim FilterIndex As Integer Dim Title As String ' Set up list of file filters Finfo = "Text Files (*.txt),*.txt," & _ "Lotus Files (*.prn),*.prn," & _ "Comma Separated Files (*.csv),*.csv," & _ "ASCII Files (*.asc),*.asc," & _ "All Files (*.*),*.*" ' Display *.* by default FilterIndex = 5 ' Set the dialog box caption Title = "Select a File to Copy To" ' Get the filename FilePathCopyTo = Application.GetOpenFilename(Finfo, _ FilterIndex, Title) ' Handle return info from dialog box If FilePathCopyTo = False Then MsgBox "No file was selected." Else MsgBox "You selected " & FilePathCopyTo End If End Sub This works fine and I changed the next bit of code in the process from Sub OpenRequote() Workbooks.Open Filename:= _ "I:\Costings\Development\2005 - 2006\Requote\requote template.xls" Range("B6").Select Windows("SAStanCosts WE 11 19 05.xls").Activate End Sub To this so that the hard coded workbook name was replaced Sub OpenRequote() Workbooks.Open FileName:= FilePathCopyTo Range("B6").Select Windows(FilePathCopyTo).Activate End Sub I am getting a run time error 9 when the code gets to Windows(FilePathCopyTo).Activate and I don’t know what to do, can anybody help? Regards Adrian -- SandyUK ------------------------------------------------------------------------ SandyUK's Profile: http://www.excelforum.com/member.php...o&userid=17487 View this thread: http://www.excelforum.com/showthread...hreadid=547362 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
run time error 9
Thanks for the reply Bob If I am understanding you correctly you are asking if i have declared the variable as Public? If so yes i have if not could you give me a little more detail? Thanks Adrian -- SandyUK ------------------------------------------------------------------------ SandyUK's Profile: http://www.excelforum.com/member.php...o&userid=17487 View this thread: http://www.excelforum.com/showthread...hreadid=547362 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
run time error 9
Well it wasn't necessarily public Sandy, but just declared, and at module
level (at least). I couldn't see in the code where the open routine was called. One got the filename, one opened it, one got the file, but which calls which, and how. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "SandyUK" wrote in message ... Thanks for the reply Bob If I am understanding you correctly you are asking if i have declared the variable as Public? If so yes i have if not could you give me a little more detail? Thanks Adrian -- SandyUK ------------------------------------------------------------------------ SandyUK's Profile: http://www.excelforum.com/member.php...o&userid=17487 View this thread: http://www.excelforum.com/showthread...hreadid=547362 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
run time error 9
I think the problem is that FilePathCopyTo is a full path, and
Window.Activate just needs the filename. However, the window with that file should be active anyway, because it's the one you just opened. I think you mean to refer to a different file (going by your original code). You're probably better off activating the workbook instead of the window - if you declare a workbook variable and set it to the workbook you can then refer to it later. Unless that it, the workbook you are trying to activate is the one with the code in (don't know if this is the case). Then you can just use ThisWorkbook. Jeff "SandyUK" wrote in message ... Hello all A while back I had to quickly put together a routine to take the information from a main workbook and copy and save aspects of the data into many different workbooks using a second workbook as a template. At the time I recorded a macro to deal with the repetitive stuff which meant that the workbook names of the workbook containing the data and the template where embedded in the code. I am trying to tidy it up by using the code below to specify the main workbook and the template. Sub GetFilePathCopyTo() Dim Finfo As String Dim FilterIndex As Integer Dim Title As String ' Set up list of file filters Finfo = "Text Files (*.txt),*.txt," & _ "Lotus Files (*.prn),*.prn," & _ "Comma Separated Files (*.csv),*.csv," & _ "ASCII Files (*.asc),*.asc," & _ "All Files (*.*),*.*" ' Display *.* by default FilterIndex = 5 ' Set the dialog box caption Title = "Select a File to Copy To" ' Get the filename FilePathCopyTo = Application.GetOpenFilename(Finfo, _ FilterIndex, Title) ' Handle return info from dialog box If FilePathCopyTo = False Then MsgBox "No file was selected." Else MsgBox "You selected " & FilePathCopyTo End If End Sub This works fine and I changed the next bit of code in the process from Sub OpenRequote() Workbooks.Open Filename:= _ "I:\Costings\Development\2005 - 2006\Requote\requote template.xls" Range("B6").Select Windows("SAStanCosts WE 11 19 05.xls").Activate End Sub To this so that the hard coded workbook name was replaced Sub OpenRequote() Workbooks.Open FileName:= FilePathCopyTo Range("B6").Select Windows(FilePathCopyTo).Activate End Sub I am getting a run time error 9 when the code gets to Windows(FilePathCopyTo).Activate and I don't know what to do, can anybody help? Regards Adrian -- SandyUK ------------------------------------------------------------------------ SandyUK's Profile: http://www.excelforum.com/member.php...o&userid=17487 View this thread: http://www.excelforum.com/showthread...hreadid=547362 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
run time error 9
Thanks for the replies Bob and Jeff Jeff you have the hit the nail on the head and simplified the problem, which is that i have a procdure that switches between 2 open workbooks using there workbook names which at present is hard coded (as below) Windows("SAStanCosts WE 11 19 05.xls").Activate I am trying to change it so the user specifies the 2 workbooks to use. I have captured the file path for each workbook but don't know how to use it to make it work in my code as if it was the hard coded workbook name? Regards Adrian -- SandyUK ------------------------------------------------------------------------ SandyUK's Profile: http://www.excelforum.com/member.php...o&userid=17487 View this thread: http://www.excelforum.com/showthread...hreadid=547362 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
run time error 9
I would set two workbook object variables, and set each as I open them. Easy
to switch between them then. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "SandyUK" wrote in message ... Thanks for the replies Bob and Jeff Jeff you have the hit the nail on the head and simplified the problem, which is that i have a procdure that switches between 2 open workbooks using there workbook names which at present is hard coded (as below) Windows("SAStanCosts WE 11 19 05.xls").Activate I am trying to change it so the user specifies the 2 workbooks to use. I have captured the file path for each workbook but don't know how to use it to make it work in my code as if it was the hard coded workbook name? Regards Adrian -- SandyUK ------------------------------------------------------------------------ SandyUK's Profile: http://www.excelforum.com/member.php...o&userid=17487 View this thread: http://www.excelforum.com/showthread...hreadid=547362 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
run time error 9
Something like this is what I was referring to.
Dim wbkStan as Workbook Workbooks.open(filepath in here) set wbkStan = activeworkbook Jeff "SandyUK" wrote in message ... Thanks for the replies Bob and Jeff Jeff you have the hit the nail on the head and simplified the problem, which is that i have a procdure that switches between 2 open workbooks using there workbook names which at present is hard coded (as below) Windows("SAStanCosts WE 11 19 05.xls").Activate I am trying to change it so the user specifies the 2 workbooks to use. I have captured the file path for each workbook but don't know how to use it to make it work in my code as if it was the hard coded workbook name? Regards Adrian -- SandyUK ------------------------------------------------------------------------ SandyUK's Profile: http://www.excelforum.com/member.php...o&userid=17487 View this thread: http://www.excelforum.com/showthread...hreadid=547362 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
run time error 9
Hi Bob I think I understand the theory of what you are suggesting and I have searched the site for workbook object variables but with no joy. Any chance you could dumb it down for me or provide a little more detail? Adrian -- SandyUK ------------------------------------------------------------------------ SandyUK's Profile: http://www.excelforum.com/member.php...o&userid=17487 View this thread: http://www.excelforum.com/showthread...hreadid=547362 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
run time error 9
Something like this
Dim oWB1 As Workbook Dim oWB2 As Workbook Set oWB1 = Workbooks.Open("C:\MyTest\File1.xls") 'do some stuff Set oWB2 = Workbooks.Open("C:\OtherDir\Subdir\File2.xls") 'now you have references to the 2 workbooks 'no need to activate, switch, or such nonsense 'now use them Call DoSomething(oWB1) Call DoSomething(oWB2) ... Sub DoSomething(oWB As Workbook) MsgBox oWB.Name End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "SandyUK" wrote in message ... Hi Bob I think I understand the theory of what you are suggesting and I have searched the site for workbook object variables but with no joy. Any chance you could dumb it down for me or provide a little more detail? Adrian -- SandyUK ------------------------------------------------------------------------ SandyUK's Profile: http://www.excelforum.com/member.php...o&userid=17487 View this thread: http://www.excelforum.com/showthread...hreadid=547362 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
run time error 9
Thanks Bob That is spot on, solved my problem and i have learnt something new :-) .. Regards Adrian -- SandyUK ------------------------------------------------------------------------ SandyUK's Profile: http://www.excelforum.com/member.php...o&userid=17487 View this thread: http://www.excelforum.com/showthread...hreadid=547362 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
run time error 9
Excellent, and apologies for calling you Sandy earlier. I remember now, you
are Adrian from Sandy Beds <g Bob "SandyUK" wrote in message ... Thanks Bob That is spot on, solved my problem and i have learnt something new :-) . Regards Adrian -- SandyUK ------------------------------------------------------------------------ SandyUK's Profile: http://www.excelforum.com/member.php...o&userid=17487 View this thread: http://www.excelforum.com/showthread...hreadid=547362 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Visual Basic Error Run Time Error, Type Mismatch | Excel Discussion (Misc queries) | |||
run-time error '1004': Application-defined or object-deifined error | Excel Programming | |||
Befuddled with For Next Loop ------ Run - Time Error '13' Type Mismatch Error | Excel Programming | |||
Code Error - Run Time Error 5 (Disable Cut, Copy & Paste) | Excel Programming | |||
Run-time error '11' & Run-time error '1004' | Excel Programming |