Multiple Excel Files to Open depending on Project

[AWPROGRAMMER]

I have some script that I have to use on multiple projects but the excel file that it opens depends on which project I am in. I do not want to have to go through the code and change the file that is opened each time. I would prefer to have a Private Function or something where I could define IF Project X then some VARIABLEA is equal to this excel file Else if Project Y then VARIABLEA is equal to this excel file and so on, that way I can keep adding projects just to the function and it can be referenced throughout the code where it asks for the workbook to be opened.

For Example I have the following code throughout my code

Set objExcel = CreateObject("excel.Application")
    
    'objExcel.Workbooks.Open (Environ("TEMP") & "\WPNoteText.xls") ' Substitute your path or file here
    objExcel.Workbooks.Open ("\\TrainingProject2009\Documents\notes.xls") ' substitute your file here
    objExcel.Visible = False

Instead of having to substiture my file for each different project, since the directories differ. I would prefer to have it where the objExcel.Workbooks.Open ("VARIABLEA")

and that VARIABLEA is defined in some sort of Private Function. I can set (1, 1) to equal the name of the project. Then maybe it can search (1,1) and then if (1,1) = Training then VariableA = \\TrainingProject2009\Documents\notes.xls

Else if (1,1) = ProjectX then VARIABLEA = \\ProjectX\Documents\notes.xls

I am not totally sure. Any help with this would be appreciated. I am assuming the private function would need to go near the beginning of the code before it reaches the "VARIABLEA" but I am not sure.

Please help.

[AWPROGRAMMER]

To make this even harder I just learned that they want the name of the project defined by the UID in the project's ini file. Each project has a seperate ini file which defines the UID = Training or whatever the project may be. So then they want that UID = Training and if that is the case then VARIABLEA = \\TrainingProject2009\Documents\notes.xls and so on based on the projects UID. Maybe this can be a subroutine instead of a Private Function, I am not toally sure.

I am definitely in over my head and could use some help.

[vb5prgrmr]

Hardcoding all those paths is really not the best thing to do because if you have to make some change to where the file is or its name, you will have to send an updated exe out and then may need to have the end user uninstall and reinstall your program. Best bet for something like this is to use or put the file to be opened in the INI file, so if changes are made, you just need to send out a new INI file or the instructions to update it.

Now, if this is a true INI file, you will want to delve into the API... WritePrivateProfileString and GetPrivateProfileString...

If it is just a text file with an INI extension, then you will want to look at...

FreeFile Function
Open Statement
Line Input Function
Input Function
Close Statement

Good Luck

[AWPROGRAMMER]

What we are doing is taking an excel file that has a list of items say 'note1', 'note2', 'note3'... and it is different for each project that we are working on. To access our projects we have to select the associated ini file. The ini file can be opened independently in Notepad. Its mainly a map of the SITESCHEMA

[SITESCHEMA]
DBTTYPE=ORACLE
ORACLE_SERVICE=GENERIC
DBSERVER=GENERIC
UID=GATCs
PWD=TG######## (etc)
DBAUSER=system
DBA=Hk###########(etc)
[DICTONARY]
DBTYPE=ORACLE
ORACLE_SERVICE=GENERIC
DBSERVER=GENERIC
UID=GATCsd
PWD=TG###########(etc)

That is an example of our ini file. To me it is a reference of which database and password to use to get to the UID. The UID is the only thing that changes from project to project. So this one in the example is GATCs and another might be SABC. The idea of the sub function or private function was to reference this ini file for the UID and from there to define what excel file was associate to it. That way in the new dll we can run it and it will know which project we are in and which excel file is associate, so when it reaches the objExcel.Workbook.Open ("") portion it will automatically know which one needs to be accessed.

[AndreRet]

If I understand this correctly, you need to determine WHICH excell file is open and then act upon that? If so you can use the following -

Dim ExcelOpen As Excel.Application

    On Error Resume Next

    Set ExcelOpen = GetObject(, "Excel.Application")
    'This can be done with any of MS Office's applications i.e. word, outlook etc.
    
    If ExcelOpen Is Nothing Then 'There is no excel app open
        'Set a path or something to open one
            Else
        txtOpenExcel.Text = ExcelOpen.ActiveWindow.Caption
        'Returns the actual name of the excel sheet as it was saved.
        'You can now work with the excell sheet i.e. if it is minimised, 
        
         ExcelOpen.WindowState = xlMaximized 'etc
   End If

You can use the text in txtOpenExcel to compare if the workbook you need is the one open or not i.e.

If Not txtOpenExcel.Text = "Note1.xls" Then
    'Open Excel Notes 1 etc .....
End If

Let me know if this helps.

[aktharshaik]

u can maintain a table in the schema itself which maps the UIDs to their respective excel files. read the data from the table using adodb or any other way u can access the data and open the specific file related to that UID.

[AWPROGRAMMER]

all of the excel files are in individual project directories. The code is part of a DLL that will be run on each project. We want the DLL to be able to recognize the project and then open the excel file that goes with that project. No Excel file is already open.

For example we have 3 projects

GATC
SABC
Training2009

we want the DLL to recognize which project is the current project in use (based on the ini file and the definition for UID) then we want it to say if it is Project GATC then EXCEL FILE = \\GATC\documents\notes.xls ELSE IF Project = SABC then EXCEL FILE = \\SABC\documents\notes.xls Else IF Project = Training2009 then EXCEL FILE = \\Training2009\documents\notes.xls

so we would have to find a way to define where to reference the UID to define what PROJECT is and then we can define what PROJECT is equal to. We would also have to define what EXCEL FILE is so that way when we say what it is equal to we can reference it later.

Then futher down in the code where it says

Set objExcel = CreateObject("excel.Application")     
objExcel.Workbooks.Open ("\\TrainingProject2009\Documents\notes.xls") ' substitute your file here    
objExcel.Visible = False

we can change it to say

Set objExcel = CreateObject("excel.Application")     
objExcel.Workbooks.Open ("EXCEL FILE") ' substitute your file here    
objExcel.Visible = False

That way, when the code ready the file to open is EXCEL FILE then it will go back to the function and recognize which project it is and what EXCEL FILE is equal to and open that associated file.

[aktharshaik]

First of all let me make the steps clear.

1. U have separate schema for each project in oracle database.
2. For each project u have related excel file in their respective folders.
3. When a project is to be opened u need to select the related ini file.
4. Once the ini file is selected, the project is opened.

Now u want to take which project opened from the ini file to access its schema.
and for that project u need to open its related excel file.

Is it what u r in need of?

[AndreRet]

It seems this might be what you are looking for -

http://www.vbaccelerator.com/home/vb/Code/Libraries/Registry_and_Ini_Files/Easy_Ini_File_Access/article.asp

This will allow you to read from your INI file, look at the part "Sections"

The following will then make sense 'I Think..":-/

Private function OpenExcel()

If lstINI.List(3) = "UID=GATC" Then
   'According to your schema in .ini
   Dim OpenExcel As String

   OpenExcel = "\\TrainingProject2009\Documents\notes.xls"
   objExcel.Workbooks.Open (OpenExcel)
ElseIf lstINI.List(3) = "UID=SABC" Then

............ ' And so forth

[AWPROGRAMMER]

AndreRet I will try what you have there, I think that is what I am looking for...but that is just to define OpenExcel, then I can place OpenExcel later on in the code to reference back to this function?

[AWPROGRAMMER]

aktharshaik
thanks for the input. yes each project has a seperate schema within oracle and an individual UID. I do not want the excel file to open each time i access the project thought. I just want a DLL that we have to reference the UID to know which excel file to open when we run the DLL but only when we run the DLL.

[AWPROGRAMMER]

This is the full code. You will notice that we have attempted to define them by project in and IF and ELSE if. The problem is that there are 3 different DLL's and they all have this and each project we add we would have to add multiple times through the source code. From reading the code below you can see how we have to do it atleast twice in this one. sometimes as many as 4 times in other DLL's. It would be much easier to have a Private Function that would allow us to define them all at once and then in the code where it is the IF and ELSE IF right now we could just put ObjExcel.WorkBook.Open("ExcelFile") and it will reference the "ExcelFile" we defined in the Private function. Does this help?

Option Explicit

Implements ILMForeignCalc



Private Function ILMForeignCalc_DoCalculate(datasource As Llama.LMADataSource, items As Llama.LMAItems, PropertyName As String, value As Variant) As Boolean

End Function

Private Function ILMForeignCalc_DoValidateItem(datasource As Llama.LMADataSource, items As Llama.LMAItems, Context As LMForeignCalc.ENUM_LMAValidateContext) As Boolean


End Function

Private Function ILMForeignCalc_DoValidateProperty(datasource As Llama.LMADataSource, items As Llama.LMAItems, PropertyName As String, value As Variant) As Boolean
  Dim Item As LMAItem
   
    datasource.PropagateChanges = True
    datasource.BeginTransaction
    
    
    ILMForeignCalc_DoValidateProperty = ValidateWPNoteText(datasource, items, PropertyName, value)
  
  
    datasource.CommitTransaction
    'ILMForeignCalc_DoValidateProperty = True
  
End Function

Private Sub ILMForeignCalc_DoValidatePropertyNoUI(datasource As Llama.LMADataSource, items As Llama.LMAItems, PropertyName As String, value As Variant)

End Sub

Private Function ValidateWPNoteText(datasource As Llama.LMADataSource, items As Llama.LMAItems, PropertyName As String, value As Variant) As Boolean
    
    Dim objLMAItem As LMAItem
    Dim objNote As LMItemNote
    Dim objModelItem As LMModelItem
    
    
    Dim NoteNumber As String
    Dim NoteText As String
    Dim RowIndex As Integer
    
    ValidateWPNoteText = False
    
    For Each objLMAItem In items
        
        NoteNumber = value
         'NoteNumber = objLMAItem.Attributes("Note.WPNoteText").value
            If LookUpWPNumber(NoteNumber, RowIndex) Then
            
                NoteText = LookUpWPNoteText(NoteNumber)
                objLMAItem.Attributes("Note.WPNoteText").value = NoteText
                
                objLMAItem.Commit
                
                ValidateWPNoteText = True
                
            Else
                value = Null
                objLMAItem.Attributes("Note.WPNoteNumber").value = Null
                objLMAItem.Attributes("Note.WPNoteText").value = Null
                objLMAItem.Commit
                
                'ValidateWPNoteText = True
                ValidateWPNoteText = False
                
                MsgBox "Please key in the Note Text!!"
                
            End If
            
    Next


End Function

Private Function LookUpWPNoteText(WPNoteNumber As String) As String

    Dim objExcel As Excel.Application
    Dim strFileName As String

    Dim SourceNumber As String
    Dim SourceText As String
    
    Dim Row As Integer
    Dim RowMax As Integer
    Row = 1
    RowMax = Row + 1
    
    Dim NumberRow As Integer
    Dim NumberRowMax As Integer
    NumberRow = 1
    
    Dim RowIndex As Integer
    RowIndex = 1
   
    Set objExcel = CreateObject("excel.Application")
    
    'objExcel.Workbooks.Open (Environ("TEMP") & "\WPNoteText.xls") ' Substitute your path or file here
    'objExcel.Workbooks.Open ("\\TrainingProject2009\Documents\notes.xls") ' Substitute your path or file here
    'objExcel.Workbooks.Open ("C:\Documents and Settings\WPNoteText.xls") ' substitute your file here
'**added LKB to set file path for active project
    Dim datasource As LMADataSource
    Dim ProjectName As String
    Dim blnUsePIDDatasource As Boolean
    If Not blnUsePIDDatasource Then
        Set datasource = New LMADataSource
    Else
        Set datasource = PIDDataSource
    End If
    ProjectName = datasource.ProjectNumber

    If ProjectName = "USHOUSTD" Then
        objExcel.Workbooks.Open ("\\\Documents\Notes\USHOUSTDNotes.xls")
    ElseIf ProjectName = "Training" Then
        objExcel.Workbooks.Open ("\\TrainingProject2009\Documents\Notes\TrainingProject2009Notes.xls")
    End If
    Set datasource = Nothing
'** LKB
    objExcel.Visible = False
    
    Do While Row < RowMax
        SourceNumber = objExcel.Worksheets("Sheet1").Cells(Row, 1).value
        If SourceNumber = "" Then
            RowMax = Row
        Else
            Row = Row + 1
            RowMax = Row + 1
        End If
    Loop
    
    Row = 1
    SourceNumber = ""
    SourceText = ""
    
    Do While NumberRow < RowMax
        'SourceNumber = objExcel.Worksheets("Sheet1").Cells(NumberRow, 1).value
        'SourceText = objExcel.Worksheets("Sheet1").Cells(NumberRow, 2).value
        
        If LookUpWPNumber(WPNoteNumber, RowIndex) Then
            SourceText = objExcel.Worksheets("Sheet1").Cells(RowIndex, 2).value
            LookUpWPNoteText = SourceText
            RowMax = NumberRow
        Else
            NumberRow = NumberRow + 1
            'MsgBox "Cannot find the matched source note. Do you want to key in the Note Text?"
        End If
    Loop
            

    objExcel.Application.Quit
    Set objExcel = Nothing

End Function

Private Function LookUpWPText() As String



End Function

Private Function LookUpWPNumber(WPNoteNumber As String, RowIndex As Integer) As Boolean


    LookUpWPNumber = False
    
    Dim objExcel As Excel.Application
    Dim strFileName As String

    Dim NotFound As Boolean
    NotFound = True
    
    Dim Row As Integer
    Dim RowMax As Integer
    Row = 1
    RowMax = Row + 1
    
    Dim RowNumber As Integer
    Dim RowMaxNumber As Integer
    RowNumber = 1
    

    Dim SourceNumber As String
    'Dim SourceText As String
   
    Set objExcel = CreateObject("excel.Application")
    
    'objExcel.Workbooks.Open (Environ("TEMP") & "\WPNoteText.xls") ' Substitute your path or file here
    'objExcel.Workbooks.Open ("\\SPPID\WPNoteText.xls") ' Substitute your path or file here
    'objExcel.Workbooks.Open ("C:\Documents and Settings\WPNoteText.xls") ' Substitute your path or file here
    'objExcel.Workbooks.Open ("\\TrainingProject2009\Documents\notes.xls") ' substitute your file here
'**added LKB to set file path for active project
    Dim datasource As LMADataSource
    Dim ProjectName As String
    Dim blnUsePIDDatasource As Boolean
    If Not blnUsePIDDatasource Then
        Set datasource = New LMADataSource
    Else
        Set datasource = PIDDataSource
    End If
    ProjectName = datasource.ProjectNumber

    If ProjectName = "USHOUSTD" Then
        objExcel.Workbooks.Open ("\\Documents\Notes\USHOUSTDNotes.xls")
    ElseIf ProjectName = "Training" Then
        objExcel.Workbooks.Open ("\\TrainingProject2009\Documents\Notes\TrainingProject2009Notes.xls")

' **** INSTEAD OF HAVING TO DEFINE EACH ONE HERE IN ELSE IF WE WOULD RATHER HAVE ' IT JUST SAY objExcel.Workbooks.Open ("ExcelFile")  ' WHERE THE EXCEL FILE IS REFERENCED BY PROJECT NAME IN A PRIVATE FUNCTION
    End If
    Set datasource = Nothing
'** LKB
    
    objExcel.Visible = False
    
    Do While Row < RowMax
        SourceNumber = objExcel.Worksheets("Sheet1").Cells(Row, 1).value
        If SourceNumber = "" Then
            RowMax = Row
        Else
            Row = Row + 1
            RowMax = Row + 1
        End If
    Loop
    
            
    SourceNumber = ""
    
    Do While RowNumber < RowMax
        SourceNumber = objExcel.Worksheets("Sheet1").Cells(RowNumber, 1).value
        
        If SourceNumber <> WPNoteNumber Then
                RowNumber = RowNumber + 1
        Else
            NotFound = False
            RowIndex = RowNumber
            RowMax = RowNumber
            'MsgBox "Cannot find the matched source note. Please key in the Note Text!"
        End If
    Loop
    
    If NotFound = False Then
        LookUpWPNumber = True
    Else
        LookUpWPNumber = False
        MsgBox "Cannot find the matched source Note Number!"
    End If
        
    
    'ActiveWorkbook.Save

    objExcel.Application.Quit
    Set objExcel = Nothing

End Function

[AndreRet]

Correct.

You first have to read into your ini file (the link I provided),

Then determine which project is running as per the list items added,

Then get your function (OpenExcel) up to give you the options to open the correct excel workbook.

You will then call the option to open (Call OpenExcel)

Good luck.

Sorry I only saw that you posted the code AFTER I replied. I will go through your code and help where I can.

[AndreRet]

I have picked up 2 mistakes on line 99 and 188 -

Set objExcel = CreateObject("excel.Application")

'You have not included the comma at (, "excel.Applicatio")

I will use your code and try and recreate exactly what you need and will post. This will only be tomorrow morning though -already 19:00 by me, going home...

I can now see where your frustation lies. There is a lot of "duplication" in there which I will try and shorten, unless someone else comes to the rescue in the meantime - vb5?