0

I have this code to copy a sheet from a Workbook in VBA/Access to another Workbook/File.

Dim File1 as String
Dim File2 as String
File1 = "D:\File1.xls"
File2 = "D:\File2.xls"

Windows(File1).Activate
Sheets("Name of Sheet").Select
Sheets("Name of Sheet").Copy Before:=Workbooks(File2).Sheets("Name of Target Sheet")

This is not working. I need to copy in background.Also to disable any macros.

  1. How can I make it work?
  2. Can I give instead of "Sheet Name" an index?
  3. Can I give an array of indexes to copy to the second Workbook?
Deduplicator
  • 44,692
  • 7
  • 66
  • 118
Adrian
  • 671
  • 4
  • 17
  • 37

2 Answers2

2

If you are running in MS Access, you need something on these lines:

Dim CopyFrom As Object
Dim CopyTo As Object ''Early binding: Workbook
Dim CopyThis As Object
Dim xl As Object ''Early binding: New Excel.Application

''Late binding
Set xl = CreateObject("Excel.Application")
xl.Visible = True

''To use a password: Workbooks.Open Filename:="Filename", Password:="Password"
Set CopyFrom = xl.Workbooks.Open("z:\docs\From.xls")
Set CopyThis = CopyFrom.Sheets(1) ''Sheet number 1
Set CopyTo = xl.Workbooks.Open("z:\docs\To.xls")
CopyThis.Copy After:=CopyTo.Sheets(CopyTo.Sheets.Count)

CopyFrom.Close False
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • Adrian It is tested in MS Access and it works. Where are you working? – Fionnuala Jul 20 '12 at 12:12
  • I tested on a simple excel.Is doing the copy of first sheet but opens the Excel. I need to copy without opening the Excel. Also I need to copy from a file with macros and login/etc. How can I disable this macros/etc from the original file...? – Adrian Jul 20 '12 at 12:26
  • The code seems valid. Not only the target file opens like you mentioned, but also the source file to make the copy. Even though the source file is closed by the code, I'm pretty sure you can see this on the screen. Greetings to Rocky ;-) – html_programmer Jul 20 '12 at 12:30
  • I made it copy the sheet. The problems is that I need this to be done on background. Is this possible?! – Adrian Jul 20 '12 at 12:33
  • 1
    Adrian, you can work with ADO and you can worked with linked tables in Access, but if you want a copy that preserves formatting etc, you will have to copy the sheet. No matter what you do, the file will be opened one way or another, although less obviously in Access & ADO. – Fionnuala Jul 20 '12 at 12:36
  • 1
    @Remou Haha, scary :-) Adrian, only thing you can do is take away the application.visible = true, which won't display the events happening. to disable macro events, use application.enableevents = false. Hope it helps. – html_programmer Jul 20 '12 at 12:41
  • @Adrian It might be possible [using ADO](http://www.rondebruin.nl/ado.htm). If you take this route, you must "set a reference to the Microsoft ActiveX Data Objects 2.5 library". – Zairja Jul 20 '12 at 12:42
  • @Zairja not in MS Access 2010 you don't. – Fionnuala Jul 20 '12 at 12:43
  • I have in the original File a Login. How can I bypass that? Application.enableevents = false is not working/Gets an error. I am tryng Set CopyFrom = xl.Workbooks.Open("D:\A01.xls") CopyFrom.EnableEvents = False Is not doing anything different. – Adrian Jul 20 '12 at 12:55
  • I added a note on supplying a password. – Fionnuala Jul 20 '12 at 12:57
  • To use a password?: Set CopyFrom = xl.Workbooks.Open Filename:="Filename", Password:="Password" Not working! – Adrian Jul 20 '12 at 13:24
0

You need to open the workbooks first.

Dim File1 As String
Dim File2 As String
File1 = "C:\Path\to\file\Book13.xlsx"
File2 = "C:\Path\to\file\Book2.xlsx"

Workbooks.Open Filename:=File2
Workbooks.Open Filename:=File1
ActiveWorkbook.Worksheets("Sheet2").Select
Sheets("Sheet1").Copy Before:=Workbooks("Book2").Sheets(1)

You can use the sheet name or an index with the Sheets object. To copy multiple worksheets into another workbook you can pass an array into a loop.

kirbs
  • 168
  • 1
  • 8