Open Excel wksheet from Access

[deadhead7]

I need to open an existing excel worksheet from Access and a process is run through a macro. Can this be done? Do you know the code?

 

[ggriffit]

check out

http://www.greggriffiths.org/webdev/both/excel/

although aimed at using VBScript to populate XL via the web, the code should be nearly identical to what you need.

 

[ActMod]

I have to credit "vbslammer" with the following, which worked great for me with respect to opening an Excel Document from Access (although I am still struggling with working with an already open Excel document).


Public Sub OpenExcel()
On Error Resume Next

Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim sht As Excel.Worksheet

Set xl = New Excel.Application 'instantiate Excel

xl.Visible = True

' use Excel's built-in open dialog

xl.Dialogs(xlDialogOpen).Show "C:\*.xls"
Set wb = xl.ActiveWorkbook

' put appropriate sheet name/index here.

Set sht = wb.Sheets(1)

' tinker with range properties

With sht
With .Range("E20", "G20&quot
.Merge
.Value = "Merged Cells"
.HorizontalAlignment = xlHAlignCenter
.Font = "Comic Sans"
.Font.Size = 18
.Font.Bold = True
.Interior.ColorIndex = 27
End With
.Activate
End With

' do more stuff...

End Sub

 

[deadhead7]

All I need is to open an existing excel workbook. can you please show me the code for that

 

[ActMod]

As best I can tell (but I am certainly no expert), it is not as easy as it should be.

The following code from the above is what opens a dialog box that lets you browse to the Excel file that you want to open. Hope this helps.

Jim



Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim sht As Excel.Worksheet

Set xl = New Excel.Application 'instantiate Excel

xl.Visible = True

' use Excel's built-in open dialog

xl.Dialogs(xlDialogOpen).Show "C:\*.xls"
Set wb = xl.ActiveWorkbook

 

[rss01]

I'm sure if this is exactly what you want, but I use this to open an excel sheet and run a macro that is in the excel sheet.

Public Sub TEST()

Dim objXLApp As Excel.Application
Dim objXLFrom As Excel.Workbook
Dim objFromSheet As Excel.Worksheet

Set objXLFrom = GetObject("C:\WORKBOOK.XLS&quot
Set objFromSheet = objXLFrom.Worksheets("SHEET 1&quot
Set objXLApp = objXLFrom.Parent


objXLApp.Visible = True
objXLFrom.Windows(1).Visible = True
objXLApp.Run "MACRO1"
objXLApp.Run "MACRO2"
objXLApp.Run "MACRO3"

objXLFrom.Save
objXLApp.Quit

Set objFromSheet = Nothing
Set objXLFrom = Nothing
Set objXLApp = Nothing

End Sub

 

[mok44]

I use the statement below to open the spreadsheet; I have queries on my spreadsheet that are set to refire and refresh data when it is open, I suppose you can do the same with a macro.

Call Shell("c:\program files\microsoft office\office10\excel.exe C:\folder\filename.xls", 1)

Good luck.

 

[deadhead7]

BY GEORGE I'VE GOT IT...
The easy way to do this is to create a button with a hyperlink to the excel file you want to open. Then make visible-false, then if using a macro that runs a bunch of queries like I'm doing to update a linked excel spreadsheet, then add a GOTO CONTROL for that button created and SENDKEYS=~. After all the update queries have run it will go to that button then enter(activates it) and it will then open up that excel spreadsheet,

Works like a charm and it was easy.

 

[rss01]

Be careful with the sendkeys. If you are not active on the program this will not work.

Say you click on the command button to run your code, while this is running you click over to outlook to read your email, when the code hits the spot to sendkeys it will send them to the application that is active.

 

[vaisag]

I have a Excel file and I want to open it through asp and retrieve data in it depending on parameters accepted in asp.

How to achieve it??

any help.........

 

[nat1967]

HI ActMod,

I was reading your response and notice you stated "although I am still struggling with working with an already open Excel document". Here is an example that first tries to use the open Excel application, if Excel is not open, it will create a new instance of it.

'*****example*****
Set objXL = GetObject(, "Excel.Application&quot
If Err.Number <> 0 Then
Set objXL = CreateObject(&quot;Excel.Application&quot
End If
'****end example****

This will allow u to use an Excel file that is already open.

HTH



Have A Great Day!!!,

Nathan
Senior Test Lead

 

[fredpeters555]

try using &quot;docmd.TransferSpreadsheet&quot; function. Go to the VBA help for this function to get arguments. The only downside is you have to know the name and or path of the xls book