Relpicating an Excel Macro in Access

[Confused Slug]

I am having difficulty replicating the action of an excel macro from Access
(really I have no idea, new to all this). Rather than having the macro in the
excel file I wish to perform the same actions but from Access. With reference
below it is step 3 where I am struggling.

1)Create a copy of the existing excel ‘Template’ spreadsheet, saving under
new name.
2)Tranfer data from Access to the new excel file in to Sheet 2.
3)Select sheet 1
Copy and paste special values to remove links / formula relying on sheet
2 data.
Delete sheet 2
Select sheet 1, cell A1
4)Close new excel file

Sample of code below. The region “Replicate excel macro†contains a copy of
the excel macro as it appears in excel. This is the bit I need to replicate.
As for the rest of the code have I got that wrong too?????


Dim xlApp As Object
Dim xlWb As Object
Dim ExcelFile As String
Dim newFileName As String

newFileName =â€D:\sample.xlsâ€
ExcelFile = "D:\Template.xls"

Set xlApp = CreateObject("Excel.Application")

‘ Open Template File
Set xlWb = xlApp.Workbooks.Open(ExcelFile)

‘ Save a copy with new file name
xlWb.SaveCopyAs newFileName

‘ Close Template file
xlWb.Close (ExcelFile)

‘ Transfer data from Access to new file
DoCmd.TransferSpreadsheet acExport ………

‘ Open new file
Set xlWb = xlApp.Workbooks.Open(newFileName)
xlApp.Visible = True

‘ Replicate excel macro
Sheets("Sheet1").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks,
:=False,Transpose:=False
Sheets("Sheet2").Select
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.Delete
Sheets("Sheet1").Select
Range("A1").Select

‘ Close new file
xlWb.Close (newFileName)

 

[Ken Snell]

Replace this code block:

' Replicate excel macro
Sheets("Sheet1").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks,
:=False,Transpose:=False
Sheets("Sheet2").Select
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.Delete
Sheets("Sheet1").Select
Range("A1").Select

With this code block:

' Replicate excel macro
Dim xlSheet As Object
Set xlSheet = xlWB.Worksheets("Sheet1")
xlSheet.Cells.Copy
xlSheet.Cells.PasteSpecial Paste:=-4163, Operation:=-4142 ,
SkipBlanks:=False,Transpose:=False
xlWB.Worksheets("Sheet2").Delete
xlSheet.Select
Set xlSheet = Nothing

 

[Confused Slug]

Thanks Ken

Dont surpose you would know have to surpress the warning meassages which
Excel generates in responce to saving the excel files or when deleting the
sheets.

I have add "xlApp.DisplayAlerts = False" before and "xlApp.DisplayAlerts =
true" after the section of code but this appears to prevent the actions from
being preformed.

 

[Douglas J. Steele]

Try

xlWb.Close True, newFileName

or (better)

xlWb.Close SaveChanges:=True, Filename:=newFileName

 

[Confused Slug]

Thanks, all sorted now
(learning slowly)

Try

xlWb.Close True, newFileName

or (better)

xlWb.Close SaveChanges:=True, Filename:=newFileName


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)





.