VBA-open excel file from word

[george hart]

I need to open an excel file from word ("C:\Documents and Settings\HartG\My Documents\Dave Slater\Import.xls")
and then run a macro from the excel file called "DelTabs".

I have the code below that I tohught would open the excel file but it's not working...Any help on this would be most apreciated.

Dim oExcel As Excel.Application
Dim oWB As Workbook
Set oExcel = New Excel.Application
Set oWB = oExcel.Workbooks.Open("C:\Documents and Settings\HartG\My Documents\Dave Slater\Import.xls")
'Rest of code
End Sub

Many thanks in advance

 

[Sektor]

[COLOR="Blue"]Sub[/COLOR] OpenExcelFile()
    
    [COLOR="Blue"]Dim[/COLOR] oExcel [COLOR="Blue"]As[/COLOR] Excel.Application
    [COLOR="Blue"]Dim[/COLOR] oWB [COLOR="Blue"]As[/COLOR] Workbook
    [COLOR="Blue"]Set[/COLOR] oExcel = [COLOR="Blue"]New[/COLOR] Excel.Application
    [COLOR="Blue"]Set[/COLOR] oWB = oExcel.Workbooks.Open("C:\Documents and Settings\HartG\My Documents\Dave Slater\Import.xls")
    [COLOR="Red"][B]oExcel.Visible = [COLOR="Blue"]True[/COLOR][/B][/COLOR]
    [COLOR="Blue"]Set[/COLOR] oWB = oExcel.Workbooks.Open(sPath)
    'Rest of code
    
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[george hart]

Many thanks. Worked a treat. i just had to remove Set oWB = oExcel.Workbooks.Open(sPath).

sorry to be a pain but do you know how I can - once the excel is open then run a macro from within the excel file?

 

[Sektor]

Adjust paths. Module1 is the module containing macro. ShowMsg is a macro which is actually laucnhed.

[COLOR="Blue"]Sub[/COLOR] OpenExcelFile()
    
    [COLOR="Blue"]Dim[/COLOR] oExcel [COLOR="Blue"]As[/COLOR] Excel.Application
    [COLOR="Blue"]Dim[/COLOR] oWB [COLOR="Blue"]As[/COLOR] Excel.Workbook
    [COLOR="Blue"]Set[/COLOR] oExcel = [COLOR="Blue"]New[/COLOR] Excel.Application
    [COLOR="Blue"]Set[/COLOR] oWB = oExcel.Workbooks.Open("k:\Test.xlsm")
    oExcel.Visible = [COLOR="Blue"]True[/COLOR]
    oWB.Application.Run "Module1.ShowMsg"

[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[george hart]

Got it, thanks very much...Most appreciated.

 

[Sektor]

Not at all! Glad that helped ya!

 

[silj]

[COLOR=Blue]Sub[/COLOR] OpenExcelFile()
    
    [COLOR=Blue]Dim[/COLOR] oExcel [COLOR=Blue]As[/COLOR] Excel.Application
    [COLOR=Blue]Dim[/COLOR] oWB [COLOR=Blue]As[/COLOR] Workbook
    [COLOR=Blue]Set[/COLOR] oExcel = [COLOR=Blue]New[/COLOR] Excel.Application
    [COLOR=Blue]Set[/COLOR] oWB = oExcel.Workbooks.Open("C:\Documents and Settings\HartG\My Documents\Dave Slater\Import.xls")
    [COLOR=Red][B]oExcel.Visible = [COLOR=Blue]True[/COLOR][/B][/COLOR]
    [COLOR=Blue]Set[/COLOR] oWB = oExcel.Workbooks.Open(sPath)
    'Rest of code
    
[COLOR=Blue]End[/COLOR] [COLOR=Blue]Sub[/COLOR]

Hi

When I run this code, I get error message "Type defined by user not defined" (translated from Portuguese, maybe not exactly this in English).

Please, how do I fix it?

 

[Ryan of Tinellb]

Hi

When I run this code, I get error message "Type defined by user not defined" (translated from Portuguese, maybe not exactly this in English).

Please, how do I fix it?

In the Visual Basic window, go to the menu bar: Tools > References.
Make sure the box for "Microsoft Excel 15.0 Object Library" (or something similar) is checked.

 

[PL dub]

Hello,

I'm using the following code to open an Excel files (located in our company server) from a Word document. It does work well but I have a little concern. Usually, when I manually open this excel file, if someone in the company has it already open, I'll receive a notification that the file is in edit mode with Mr.Someone and give me the choice to
1- "Read-Only" (Open it in Read-Only)
2- "Notify "Open it and get notify when the other user exit the excel so I became in edit mode"
3 - Cancel

When using the below code, if someone has the file already open, it will open directly in Read-Only and I will never get that little pop-up that give me the 3 choices. Can I modify the code to get that pop-up before the file is open ?

Code :
Sub OpenIndex()


Dim Index As String
Dim oExcel As Excel.Application
Dim oWB As Excel.Workbook

Index = "G:\xxx\xxx\xx\Index.xlsx"

Set oExcel = New Excel.Application
Set oWB = oExcel.Workbooks.Open(Index)
oExcel.Visible = True


End Sub

 

[PL dub]

Any idea ?

See the "pop-up" below I'm talking about. When using my Macro, if the file is already in use, it will open it automatically in Read Only in lieu of showing that window. Can I modify my code to have this window appears when the file is already in use by someone else ?

[COLOR=#333333]Sub OpenIndex()[/COLOR]

[COLOR=#333333]Dim Index As String[/COLOR]
[COLOR=#333333]Dim oExcel As Excel.Application[/COLOR]
[COLOR=#333333]Dim oWB As Excel.Workbook[/COLOR]

[COLOR=#333333]Index = "G:\xxx\xxx\xx\Index.xlsx"[/COLOR]

[COLOR=#333333]Set oExcel = New Excel.Application[/COLOR]
[COLOR=#333333]Set oWB = oExcel.Workbooks.Open(Index)[/COLOR]
[COLOR=#333333]oExcel.Visible = True[/COLOR]


[COLOR=#333333]End Sub[/COLOR]