-
June 28th, 2010, 03:35 PM
#1
set objexcel question
I have multiple projects wanting to use the same report. instead of me having to edit the report to include their directory is there a way to group then all together in an if, then function and then have it referenced throughout the code?
currently the following appears numerous times in my code...
Set objExcel = CreateObject("excel.Application")
'objExcel.Workbooks.Open (Environ("TEMP") & "\WPNoteText.xls") ' Substitute your path or file here
'objExcel.Workbooks.Open ("\\sppidservices\SPPID\WPNoteText.xls") ' substitute your file here
objExcel.Workbooks.Open ("\\TrainingProject2009\Documents\notes.xls") ' substitute your file here
objExcel.Visible = False
*** TrainingProject2009 is one of my projects but I have many others. Is there a way to write a function or private function that will say something like...
Private Function ActiveProject As String
(put some code in here that checks column and row 1,1 for the "ActiveProject"
(define that ActiveWorkBook will be equal to an actual value)
If ActiveProject = trainingproject2009 then Set ActiveWorkbook = ("\\TrainingProject2009\documents\notes.xls")
Else if ActiveProject = Project1 then Set ActiveWorkBook = ("project1\documents\notes.xls")
Else if ActiveProject = Project2 then Set ActiveWorkBook = ("project2\documents\notes.xls")
END FUNCTION
****** Then later in the code where it says...
Set objExcel = CreateObject("excel.Application")
'objExcel.Workbooks.Open (Environ("TEMP") & "\WPNoteText.xls") ' Substitute your path or file here
'objExcel.Workbooks.Open ("\\sppidservices\SPPID\WPNoteText.xls") ' substitute your file here
objExcel.Workbooks.Open ("\\ushouwpcad02\TrainingProject2009\Documents\notes.xls") ' substitute your file here
objExcel.Visible = False
we can substitute it for the value of
Set objExcel = CreateObject("excel.Application")
'objExcel.Workbooks.Open (Environ("TEMP") & "\WPNoteText.xls") ' Substitute your path or file here
'objExcel.Workbooks.Open ("\\sppidservices\SPPID\WPNoteText.xls") ' substitute your file here
objExcel.Workbooks.Open ("ActiveWorkBook") ' substitute your file here
objExcel.Visible = False
and then that value of ActiveWorkBook will reference back to our Private function each time. So then when I add more projects I can just add the once to that portion of the code instead of having to edit it throughout the code for each individual project.
Any suggestions would be greatly appreciated.
*** I want to be able to have this as a function i think that is written out one time and then everywhere else throughout the report I can just
-
June 30th, 2010, 03:12 AM
#2
Re: set objexcel question
Hmmm... I see you did not update this query with the ini information as you did at the other site...
As I said over there, store it in the ini file...
Good Luck
-
July 2nd, 2010, 11:27 PM
#3
Re: set objexcel question
Hi, AwProgrammer
Please reread forum rules and then correct your post using correct code Tags .
Last edited by firoz.raj; July 2nd, 2010 at 11:37 PM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|