Activate Open Excel file

[JPearson]

I am importing Excel data into Access.
I have code already writen that pulls the sheet names from user selected excel files, populates a form with the sheet names(opens files to get names and closes), allows the user to chose with Sheets to import, and then imports those sheets(opens and reformats sheets if needed then closes). The issue I have is that I am opening and closeing the Excel file 2 times during this process.
I would like to open the workbook and then be able to reselect the workbook later but am having difficulty in getting the code correct in Access.

Heres a snippet of code where the file is opened for the second time where I would like to just activate the file instead.

Help?

Public Sub FormatExcelSheet()
' removes excess row data from spreadsheet
Dim AppExcel As Object
Set AppExcel = CreateObject("Excel.Application")
Dim ColDel As Integer
 
 
AppExcel.workbooks.Open FileNameO
AppExcel.Visible = False
'    AppExcel.Visible = True
AppExcel.sheets(WSOrderName).Activate
rownumber = AppExcel.ActiveSheet.UsedRange.columns.Count
AppExcel.columns(1).select
    Do
        If AppExcel.activecell.Value <> "" Then
            AppExcel.activecell.offset(0, 1).select
        End If
    Loop Until AppExcel.activecell = ""

 

[chergh]

Remove the code that closes the book and then you can keep referring to it.

 

[boblarson]

Also, instead of declaring

Dim AppExcel As Object

in the Sub - you can set it as a global variable so that it can be instantiated for however long you wish.

 

[JPearson]

I have 2 workbooks open. How do I identify which one I want to use? I can not seem to tell Access which one to use.

 

[boblarson]

I have 2 workbooks open. How do I identify which one I want to use? I can not seem to tell Access which one to use.

Each one should use a separate variable and then you can refer back to it at any time. And just as an ADDENDUM to my reference to the global variable - if you have

Dim AppExcel As Object

in a procedure and that procedure runs again it will reinstantiate that AppExcel object so then it can't be used to refer to the same instance that is open.

 

[JPearson]

Ok.. to make sure I understand correctly I make 2 global var.
Dim AppExcelOr as object
Dim AppExcelRs as object

and just refer to which ever instance I need.

Each one should use a separate variable and then you can refer back to it at any time. And just as an ADDENDUM to my reference to the global variable - if you have

Dim AppExcel As Object

in a procedure and that procedure runs again it will reinstantiate that AppExcel object so then it can't be used to refer to the same instance that is open.

 

[boblarson]

You don't really need a separate instance of Excel, but a separate WORKBOOK object would do so you can refer to the appropriate workbook. But yes, you can also do it that way.

And the global variable would be set in a standard module and would not be using DIM but

Public AppExcelOr as object
Public AppExcelRs as object

if you wanted to be able to refer from any location or

Private AppExcelOr as object
Private AppExcelRs as object

if accessed only in that same module.

 

[chergh]

This will open two workbooks and you can refer to them as wb1 and wb2.

Public Sub FormatExcelSheet()
' removes excess row data from spreadsheet
Dim AppExcel As Object


dim wb1 as object
dim wb2 as object

Set AppExcel = CreateObject("Excel.Application")

set wb1 = AppExcel.workbooks.Open(FileNameO)
set wb2 = AppExcel.workbooks.Open(OtherFilePathAndName)

 

[Toolbit1]

I'm trying to do something similar as this but using an excell file from our network. How do I do that?

Toolbit1

 

[JPearson]

My excel file comes from a shared drive on our nextwork. I will have to find where I Got the class from but it uses the windows file dialog to locate the file and get the file path/string.

If the file is the same path/string you could just hardcode that.

 

[Toolbit1]

Can you copy and paste your code so I can get an idea of what it looks like?

Toolbit1

 

[JPearson]

The dialog box code came from here: http://www.tek-tips.com/faqs.cfm?fid=2484

I changed it so I could pull the Filename as a public variable to pass between the different modules I had.

Option Compare Database
Public FileNameO, FileNameR As String 'full file name

And then open the file with

Dim AppExcel As Object
Set AppExcel = CreateObject("Excel.Application")
Dim ColDel As Integer
 
 
AppExcel.workbooks.Open FileNameO