Manipulating Excel from Access vba

  • Thread starter Thread starter Erez
  • Start date Start date
E

Erez

dear all
in my access project, i need to move a certain worksheet
from one xl file to another. can someone give me an
explanation on doing that?
thanx a lot
Erez.
 
This is clipped from code I have that creates a master
workbook with several sheets each of which need to be
distributed to a specific department. So it then copies
each sheet to a new workbook and saves it (according to
SavePath and SubSheets(n) which is defined along the
way). sBookName is used to capture the original workbook
name

Dim xlApp As New Excel.Application

' Get name of active workbook
sBookName = xlApp.ActiveWorkbook.Name
' copy active sheet to a new workbook
xlApp.ActiveWorkbook.ActiveSheet.Copy
' save the NEW workbook
xlApp.ActiveWorkbook.SaveAs SavePath & "\" & SubSheets
(j).Name & ".xls"
' close the NEW workbook
xlApp.ActiveWorkbook.Close
' go back to the original workbook
xlApp.Workbooks(sBookName).Activate
 
Back
Top