CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2010
    Posts
    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

  2. #2
    Join Date
    Apr 2009
    Posts
    394

    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

  3. #3
    Join Date
    Dec 2008
    Location
    Step Into(F11)
    Posts
    465

    Smile 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
  •  





Click Here to Expand Forum to Full Width

Featured