automation with excel

[john]

Is it possible to open a specific excel spreadsheet and
specific worksheet through access vb code?

 

[PC Datasheet]

John,

Take a look at the TransferSpreadsheet function in the Help file.


--
PC Datasheet
A Resource for Access, Excel and Word Applications
(e-mail address removed)
www.pcdatasheet.com

· Design and basic development for new applications
· Additions, Modifications and "Fixes" for existing applications
· Mentoring for do-it-yourselfers who want guidance
· Complete application design and development
· Applications Using Palm Pilot To Collect Data And
Synchronize The Data Back To Access Or Excel

 

[john]

I tried the transferSpreadsheet method but It does not
allow you to edit cells in particular.
Unless I missed something, it overwrites all of the cells.

 

[Douglas J. Steele]

Take a look at http://www.mvps.org/access/modules/mdl0006.htm at "The Access
Web". It shows you how to address specific cells in a spreadsheet through
Automation.

--
Doug Steele, Microsoft Access MVP



I tried the transferSpreadsheet method but It does not
allow you to edit cells in particular.
Unless I missed something, it overwrites all of the cells.

 

[john]

I am already using code similar to that but I want to
code to open a specific workbook and worksheet without
user intervention before I update the specific cells.

-----Original Message-----
Take a look at

http://www.mvps.org/access/modules/mdl0006.htm at "The
Access

 

[Douglas J. Steele]

Replace

objXL.Application.workbooks.Add
Set objActiveWkb = objXL.Application.ActiveWorkBook

with something like

objXL.Application.Workbooks.Open "C:\MyFolder\MyBook.xls"
Set objActiveWkb = objXL.Application.WorkBooks("MyBook.xls")

Replace

.Worksheets(1).Cells(1, 1) = "Hello World"

with something like

.Worksheets("Sheet1").Cells(1, 1) = "Hello World"

 

[john]

Thanks,
That is great. Your help is greatly appreciated.
John.

-----Original Message-----
Replace

objXL.Application.workbooks.Add
Set objActiveWkb = objXL.Application.ActiveWorkBook

with something like

objXL.Application.Workbooks.Open "C:\MyFolder\MyBook.xls"

 

[john]

Thanks again, this is working fine but now when the excel
closes it does not save the update. Is there a property
such as .update that I need to use?
John.

-----Original Message-----
Take a look at

http://www.mvps.org/access/modules/mdl0006.htm at "The
Access

 

[Douglas J. Steele]

I never noticed that the sample code in that example deliberately doesn't
save!

Change

objActiveWkb.Close savechanges:=False

to

objActiveWkb.Close savechanges:=True

(or leave out the savechanges:=<value> completely, and it'll prompt the user
to save if changes were made)

 

[john]

Thanks again.

-----Original Message-----
I never noticed that the sample code in that example deliberately doesn't
save!

Change

objActiveWkb.Close savechanges:=False

to

objActiveWkb.Close savechanges:=True

(or leave out the savechanges:=<value> completely, and it'll prompt the user
to save if changes were made)


--
Doug Steele, Microsoft Access MVP






.