We help IT Professionals succeed at work.
Get Started

MS Access VBA to copy Workbook Worksheet to another Workbook

CFMI
CFMI asked
on
4,411 Views
Last Modified: 2013-05-21
Hello Experts,

Please provide assistance to include a MS Access table field that displays the Path and the File Name along with the Tab name as I am receiving a Run Time Error 1004 that displays " Output 1.xlsx could not be found".  I first tried the Spreadsheet Name then entered the Spreadsheet Tab inside the boded below statement – this is where the code stops. The Access table looks like:
ID      SpreadsheetName                                                                            SpreadsheetTab
1      X:\2013 reports\cfi consol nod 0313.xlsm                                       Output 1
2      X:\2013 reports\cfmi consol 0313.xlsm                                           Output 1
3      X:\Reports Close\CFMI Pre_Close_w_filters_RiskNEW.xlsm        Output 1 (100.100.T05)

and the VBA:
Private Sub WorksheetsCopy_Click()
Dim CopyFrom As Object
Dim CopyTo As Object
Dim CopyThis As Object
Dim xl As Object
Dim SpreadsheetName As String
Dim SpreadsheetTab As String
Dim ID As Field
Dim rs As Recordset
Dim sh As Worksheet
Dim oldPath As String, newPath As String
''Late binding
Set xl = CreateObject("Excel.Application")
xl.Visible = True
newPath = "H:\PDF"
Set rs = CurrentDb.OpenRecordset("TblReports")
Do While Not (rs.EOF)
Dim wkbSource As Excel.workbook
Dim wkbDest As Excel.workbook
Set wkbSource = Workbooks.Open(xl.Workbooks.Open(rs("SpreadsheetTab")))
Set wkbDest = Workbooks.Open("H:\PDF\MasterReport.xlsx")
    'Opens workbook
   Workbooks.Open "wkbSource"
   'Makes a copy of "Sheet1"
    ActiveWorkbook.Sheets("Sheet1").Copy _
       after:=ActiveWorkbook.Sheets("Sheet1")
       'Copies that copy to "wkbDest" workbook
        ActiveSheet.Move Before:=Workbooks("wkbDest").Sheets(1)
        ActiveSheet.Next.Select
       'Closes "wkbDest" workbook and saves the copied sheeet
       ActiveWorkbook.Close SaveChanges:=True
  rs.MoveNext
CopyFrom.Close False
Loop
rs.Close
End Sub

Thanks,
Bob
Comment
Watch Question
CERTIFIED EXPERT
Top Expert 2010
Commented:
This problem has been solved!
Unlock 1 Answer and 9 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE