Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with my coding problem?
I'm hoping that someone can help me figure out a coding problem that I
SCREWED UP. I had this code in my Microsoft Word VB Editor that you see below. The code worked PERFECTLY until I stupidly tried to edit it without first making a backup. I know, first rule of any work is to make a backup, so I can't believe I forgot to do that. As an FYI, the "code" contained below was interfacing with one of my Excel Workbooks, and allowed me to chose a client name from a dropdown menu (in Excel), and when the name was chosen, Excel would call up a "Word", and the Userform that was created would also open. This would then allow me to input narrative comments into the Userform. As a result of my "editing", I can select a client name (on the Excel Workbook), but "Word" and "The Userform" no longer opens (actually it looks as if it is trying to open, but cannot). If some kind and knowledge person could look at the code below and let me know if you see anything that I am leaving out I'd greatly appreciate it. I've also emailed a few friends for suggestions, but wanted to post here as well in order to attempt a "resolve" as soon as possible. I so depend upon computer programs to do their job, so when they don't (thanks to my errors) I am quite frustrated. Dan The Code in the MS Word "Sample Note.doc" VB Editor Module is as follows: Sub UpdateComments(parm1) On Error Resume Next Documents.Open FileName:=parm1 If Not FileExists(parm1) Then ' Check if file/folder exists ..... MsgBox "File " & parm1 & " does not exist", vbOKCancel Exit Sub End If UserForm1.Show vbModeless End Sub __________________________________________________ ___________________ Function FileExists(fname) As Boolean ' Returns TRUE if the file exists Dim x As String x = Dir(fname) If x < "" Then FileExists = True _ Else FileExists = False End Function |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with my coding problem?
Hi Dan,
I tested the code with a dummy entry for the document to be opened. I took out the parameter from the sub name and assigned a document name to parm1 like this:- Sub UpdateComments() parm1 = "Document1.doc" The code works perfectly. Therefore I suggest that you test to ensure that the code is attempting to open the document from the correct folder/path. Put a stop or interupt in after the line:- Documents.Open FileName:=parm1 Stop Run the code and when it stops rest the mouse pointer over parm1. Check that parm1 is the correct filename. If only the filename is displayed without a path, then check that your current path is where the document is. Check this by going into file-open and see if you are in the correct path. If parm1 includes a path then ensure that it is correct. If not in the correct path then you can either prefix parm1 with the fully qualified path in your code or you can use code to change to the correct path before the code to open the document. Regards, OssieMac "Dan the Man" wrote: I'm hoping that someone can help me figure out a coding problem that I SCREWED UP. I had this code in my Microsoft Word VB Editor that you see below. The code worked PERFECTLY until I stupidly tried to edit it without first making a backup. I know, first rule of any work is to make a backup, so I can't believe I forgot to do that. As an FYI, the "code" contained below was interfacing with one of my Excel Workbooks, and allowed me to chose a client name from a dropdown menu (in Excel), and when the name was chosen, Excel would call up a "Word", and the Userform that was created would also open. This would then allow me to input narrative comments into the Userform. As a result of my "editing", I can select a client name (on the Excel Workbook), but "Word" and "The Userform" no longer opens (actually it looks as if it is trying to open, but cannot). If some kind and knowledge person could look at the code below and let me know if you see anything that I am leaving out I'd greatly appreciate it. I've also emailed a few friends for suggestions, but wanted to post here as well in order to attempt a "resolve" as soon as possible. I so depend upon computer programs to do their job, so when they don't (thanks to my errors) I am quite frustrated. Dan The Code in the MS Word "Sample Note.doc" VB Editor Module is as follows: Sub UpdateComments(parm1) On Error Resume Next Documents.Open FileName:=parm1 If Not FileExists(parm1) Then ' Check if file/folder exists ..... MsgBox "File " & parm1 & " does not exist", vbOKCancel Exit Sub End If UserForm1.Show vbModeless End Sub __________________________________________________ ___________________ Function FileExists(fname) As Boolean ' Returns TRUE if the file exists Dim x As String x = Dir(fname) If x < "" Then FileExists = True _ Else FileExists = False End Function |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with my coding problem?
Still can't get it to work on my end. I was able to utilize "stop" to bring
up the Userform again, however I think that I'm still missing something because my various Userform buttons (print, add note, etc) aren't working with the addition of the stop code...........I appreciate however your feedback OssieMac and your time. "OssieMac" wrote: Hi Dan, I tested the code with a dummy entry for the document to be opened. I took out the parameter from the sub name and assigned a document name to parm1 like this:- Sub UpdateComments() parm1 = "Document1.doc" The code works perfectly. Therefore I suggest that you test to ensure that the code is attempting to open the document from the correct folder/path. Put a stop or interupt in after the line:- Documents.Open FileName:=parm1 Stop Run the code and when it stops rest the mouse pointer over parm1. Check that parm1 is the correct filename. If only the filename is displayed without a path, then check that your current path is where the document is. Check this by going into file-open and see if you are in the correct path. If parm1 includes a path then ensure that it is correct. If not in the correct path then you can either prefix parm1 with the fully qualified path in your code or you can use code to change to the correct path before the code to open the document. Regards, OssieMac "Dan the Man" wrote: I'm hoping that someone can help me figure out a coding problem that I SCREWED UP. I had this code in my Microsoft Word VB Editor that you see below. The code worked PERFECTLY until I stupidly tried to edit it without first making a backup. I know, first rule of any work is to make a backup, so I can't believe I forgot to do that. As an FYI, the "code" contained below was interfacing with one of my Excel Workbooks, and allowed me to chose a client name from a dropdown menu (in Excel), and when the name was chosen, Excel would call up a "Word", and the Userform that was created would also open. This would then allow me to input narrative comments into the Userform. As a result of my "editing", I can select a client name (on the Excel Workbook), but "Word" and "The Userform" no longer opens (actually it looks as if it is trying to open, but cannot). If some kind and knowledge person could look at the code below and let me know if you see anything that I am leaving out I'd greatly appreciate it. I've also emailed a few friends for suggestions, but wanted to post here as well in order to attempt a "resolve" as soon as possible. I so depend upon computer programs to do their job, so when they don't (thanks to my errors) I am quite frustrated. Dan The Code in the MS Word "Sample Note.doc" VB Editor Module is as follows: Sub UpdateComments(parm1) On Error Resume Next Documents.Open FileName:=parm1 If Not FileExists(parm1) Then ' Check if file/folder exists ..... MsgBox "File " & parm1 & " does not exist", vbOKCancel Exit Sub End If UserForm1.Show vbModeless End Sub __________________________________________________ ___________________ Function FileExists(fname) As Boolean ' Returns TRUE if the file exists Dim x As String x = Dir(fname) If x < "" Then FileExists = True _ Else FileExists = False End Function |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with my coding problem?
Hi again Dan,
Very little works while the macro is interrupted on the stop code. It simply allows you to hover the mouse pointer over variables to confirm their contents. I should have told you that you need to press F5 to continue after the stop. Were you able to confirm the contents of parm1 and also your current path because I still suspect that this is the problem. As a little extra info, the On Error Resume Next is there in case the file cannot be opened. You should cancel it after it has done it's job like this:- On Error Resume Next Documents.Open FileName:=parm1 On Error GoTo 0 'Cancels the previous On Error Resume Next If you do not cancel it then you are going to miss real errors in your code. It will simply skip past them. Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to fix the coding? | Excel Worksheet Functions | |||
coding | Excel Discussion (Misc queries) | |||
Coding | Excel Discussion (Misc queries) | |||
First attempt at VBA coding problem | New Users to Excel | |||
coding | Excel Discussion (Misc queries) |