Manip. Excel through Access VBA 
Author Message
 Manip. Excel through Access VBA

Hello,

I've a module in MS Access (VBA). Which I open an instance
of Excel(Existing spreadsheet) and try to manipulate the
contents.
I can get so far, the code runs and nothing changes in excel.
I'm trying to select a SHEET in excel and change the name!!!

Here's the code I'm using, can anybody see the problem ??

Thanks for any help ......
Tony.

***********************************************************
***********************************************************
Dim MyExcel As Excel.Application
Dim MyWbook As Excel.Workbook
Dim MySheet As Excel.Sheets
Set MyExcel = New Excel.Application

With MyExcel
    .Visible = True
    Set MyWbook = .Workbooks.Open("C:\DDA_CB.xls")
    On Error Resume Next
    Set MySheet = MyWbook.Worksheets("Qry_Rpt_PL_Summary")
    '''''''Sheets("Sheet2").Select
    '''''''Sheets("Qry_Rpt_PL_Summary").Name = "CB_Summary"
    MySheet.Select
    MySheet("Qry_Rpt_PL_Summary").Name = "CB_Summary"
End With

'Save file...
MyExcel.Application.DisplayAlerts = False  ' Turn off screen Alerts ...
MyExcel.ActiveWorkbook.SaveAs Filename:="C:\DDA_CB.xls", FileFormat:=xlNormal, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False
MyExcel.Application.DisplayAlerts = True
MyExcel.Quit



Tue, 04 Nov 2003 22:54:04 GMT  
 Manip. Excel through Access VBA
Hi Tony,

I've made a few changes to the code, hope it works for you...

Sub SubRenameSheet()
Dim MyExcel As Excel.Application
Dim MyWbook As Excel.Workbook
Dim MySheet As Excel.Sheets
Set MyExcel = New Excel.Application

With MyExcel
  .Visible = True
  Set MyWbook = .Workbooks.Open("C:\SamplesTMP\table1.xls")
  MyWbook.Worksheets("Sheet1").Name = "CB_Summary"
End With

'Save file...
MyWbook.Save
MyWbook.Close
Set MySheet = Nothing
Set MyWbook = Nothing
MyExcel.Quit
Set MyExcel = Nothing

End Sub

hth
On Fri, 18 May 2001 07:54:04 -0700, "Tony Sheehan"

Quote:

>Hello,

>I've a module in MS Access (VBA). Which I open an instance=20
>of Excel(Existing spreadsheet) and try to manipulate the=20
>contents.
>I can get so far, the code runs and nothing changes in excel.
>I'm trying to select a SHEET in excel and change the name!!!

>Here's the code I'm using, can anybody see the problem ??

>Thanks for any help ......=20
>Tony.

>***********************************************************
>***********************************************************
>Dim MyExcel As Excel.Application
>Dim MyWbook As Excel.Workbook
>Dim MySheet As Excel.Sheets
>Set MyExcel =3D New Excel.Application

>With MyExcel
>    .Visible =3D True
>    Set MyWbook =3D .Workbooks.Open("C:\DDA_CB.xls")
>    On Error Resume Next
>    Set MySheet =3D MyWbook.Worksheets("Qry_Rpt_PL_Summary")
>    '''''''Sheets("Sheet2").Select
>    '''''''Sheets("Qry_Rpt_PL_Summary").Name =3D "CB_Summary"
>    MySheet.Select
>    MySheet("Qry_Rpt_PL_Summary").Name =3D "CB_Summary"
>End With

>'Save file...
>MyExcel.Application.DisplayAlerts =3D False  ' Turn off screen Alerts =
>...
>MyExcel.ActiveWorkbook.SaveAs Filename:=3D"C:\DDA_CB.xls", =
>FileFormat:=3DxlNormal, _
>        Password:=3D"", WriteResPassword:=3D"", =
>ReadOnlyRecommended:=3DFalse, _
>        CreateBackup:=3DFalse
>MyExcel.Application.DisplayAlerts =3D True
>MyExcel.Quit

---------
Pedro Gil
http://www.geocities.com/pmpg98_pt

DDPI
http://www.users.bigpond.com/papwalker/DDPI.HTML
https://sourceforge.net/projects/ddpi/



Wed, 05 Nov 2003 00:25:12 GMT  
 Manip. Excel through Access VBA
Hi Pedro,

Thanks....
That worked lovely....

Quote:
-----Original Message-----

Hi Tony,

I've made a few changes to the code, hope it works for you...

Sub SubRenameSheet()
Dim MyExcel As Excel.Application
Dim MyWbook As Excel.Workbook
Dim MySheet As Excel.Sheets
Set MyExcel = New Excel.Application

With MyExcel
  .Visible = True
  Set MyWbook = .Workbooks.Open("C:\SamplesTMP\table1.xls")
  MyWbook.Worksheets("Sheet1").Name = "CB_Summary"
End With

'Save file...
MyWbook.Save
MyWbook.Close
Set MySheet = Nothing
Set MyWbook = Nothing
MyExcel.Quit
Set MyExcel = Nothing

End Sub

hth
On Fri, 18 May 2001 07:54:04 -0700, "Tony Sheehan"

>Hello,

>I've a module in MS Access (VBA). Which I open an instance=20
>of Excel(Existing spreadsheet) and try to manipulate the=20
>contents.
>I can get so far, the code runs and nothing changes in excel.
>I'm trying to select a SHEET in excel and change the name!!!

>Here's the code I'm using, can anybody see the problem ??

>Thanks for any help ......=20
>Tony.

>***********************************************************
>***********************************************************
>Dim MyExcel As Excel.Application
>Dim MyWbook As Excel.Workbook
>Dim MySheet As Excel.Sheets
>Set MyExcel =3D New Excel.Application

>With MyExcel
>    .Visible =3D True
>    Set MyWbook =3D .Workbooks.Open("C:\DDA_CB.xls")
>    On Error Resume Next
>    Set MySheet =3D MyWbook.Worksheets("Qry_Rpt_PL_Summary")
>    '''''''Sheets("Sheet2").Select
>    '''''''Sheets("Qry_Rpt_PL_Summary").Name =3D "CB_Summary"
>    MySheet.Select
>    MySheet("Qry_Rpt_PL_Summary").Name =3D "CB_Summary"
>End With

>'Save file...
>MyExcel.Application.DisplayAlerts =3D False  ' Turn off screen Alerts =
>...
>MyExcel.ActiveWorkbook.SaveAs Filename:=3D"C:\DDA_CB.xls", =
>FileFormat:=3DxlNormal, _
>        Password:=3D"", WriteResPassword:=3D"", =
>ReadOnlyRecommended:=3DFalse, _
>        CreateBackup:=3DFalse
>MyExcel.Application.DisplayAlerts =3D True
>MyExcel.Quit

---------
Pedro Gil
http://www.geocities.com/pmpg98_pt

DDPI
http://www.users.bigpond.com/papwalker/DDPI.HTML
https://sourceforge.net/projects/ddpi/
.



Wed, 05 Nov 2003 00:52:53 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Manip. Excel - From VBA in Access

2. Running excel add-in (added into excel - not access) in Access VBA

3. Access VBA Calling MSWord/Excel VBA?

4. VBA Analysis Tookpack- Excel In Access VBA

5. vba access vs vba excel (alguien sabe?)

6. Windows EXCEL VBA / MAC EXCEL VBA compatibility issues

7. Excel 97 VBA vs Excel 2000 VBA

8. Edit Access Record in Excel, then send back to Access using VBA

9. Run Excel Macro through Access VBA

10. VBA : From ACCESS to EXCEL 2002 !

11. Setting Excel cell formula via Access VBA

12. How to use the excel Solver in Access VBA

 

 
Powered by phpBB® Forum Software