Opening Excel Workbook with Different Name

[caleno]

Hi I need to re-use code as much as possible. I need to use the same procedure to open three different workbooks (one at a time) but it is not working.

This is what I have

Public Sub AddRow(RowData As String, NameFile As String)
    Dim oXLApp As Excel.Application       
    Dim oXLBook As Excel.Workbook
    Dim objExcelCI As Excel.Chart
    Dim oXLsheet As Excel.Worksheet
      
    Set oXLApp = New Excel.Application  'Create a new instance of Excel
    Set oXLBook = oXLApp.Workbooks.Open("C:\log\NameFile.xls")  
    Set oXLsheet = oXLBook.Worksheets(1)

End Sub

How should I define NameFile (String?) so that I could use it in this line
Set oXLBook = oXLApp.Workbooks.Open("C:\log\NameFile.xls")
with one of three names I have.
How should I call this function from any other sub?

Thanks

[RobDog888]

NameFile needs to be string like you have it but also used as a variable, which it is but not in your open statement, by escaping the double quotes like so...

Set oXLBook = oXLApp.Workbooks.Open("C:\log\" & NameFile & ".xls")

[RobDog888]

Also, you shouldnt be creating a new excel application object everytime you call the funciton. It will really be slow and take up resources.

Dimension the variables that need to persist in a module and just test to see if they are created yet or not and create based upon need.

[caleno]

NameFile needs to be string like you have it but also used as a variable, which it is but not in your open statement, by escaping the double quotes like so...

Set oXLBook = oXLApp.Workbooks.Open("C:\log\" & NameFile & ".xls")

NameFile As String ' is this correct?

Public Function ProcessBuffer()

Dim NameFile As String

NameFile = AllData
    AddRow sNewVariable, NameFile
End Function

If I call the AddRow function to open a File named AllData, it gives an error highlighting AllData, saying "Variable not defined"
How should I define AllData when is a constant file name?
How should I do this?

[RobDog888]

1. Yes
2. Const AllData As String = "Blah"

[westconn1]

if alldata is a constant namefile will always be the same value, so will always open the same file, you need to make namefile = to the name of the correct workbook each time.

if you want to process the same 3 files every time then possibly pass a string or array of the 3 file names, then make a loop in the addrow sub to open each of the 3 files in turn