Copying and Pasting Excel Sheets From Access

[CPGDeveloper]

I'm working on an Access Application -- through this application, I would like to be able to copy and paste a worksheet from one Excel workbook (which I'll call 'Previous' to the identically titled worksheet in an other workbook (which I'll call 'Current').

I've defined the two workbooks and sheets:

'prevmon, curmon, and dSheet have already been defined
Set prevxl = CreateObject("Excel.Application")
Set prevwk = prevxl.Workbooks.Open(prevmon)
Set curxl = CreateObject("Excel.Application")
Set curwk = curxl.Workbooks.Open(curmon)
Set prevsht = prevwk.Sheets(dSheet)
Set cursht = curwk.Sheets(dSheet)


I've tried several variations of the following to copy and paste, with no success:
prevsht.Range.Cells.Copy (cursht.Range("A1"))

I'm assuming it's a simple syntax that I'm missing -- any ideas would be greatly appreciated. Thank you in advance for your time.

 

[ranman256]

you dont copy/paste excel sheets to access.
you import data via:
docmd.transferspreadsheet...
or
attach the workbook as an external table, then run an append query

 

[CPGDeveloper]

I'm not trying to import data to Access. I'm trying to use MS Access VBA to copy and paste an excel worksheet from one workbook to another. I am able to do this on a cell by cell basis (Copy Cell A1 from one sheet and pasting it into cell B1 into another), but I'm trying to copy and paste an entire sheet.

I cannot seem to figure out the exact syntax, as I've laid out in the original post -- any help would be greatly appreciated, Thanks.

 

[CPGDeveloper]

I couldn't find a syntax that wouldn't error out, so I ended up writing a Loop copying and pasting cell by cell, essentially coping and pasting A1 to Z250. There has to be a better way of doing this, but this works --

'thisisstupid defined as a variant
Do Until b = 27
If prevsht.Cells(a, b).HasFormula = True Then
thisisstupid = prevsht.Cells(a, b).Formula
Else
thisisstupid = prevsht.Cells(a, b).Formula
End If
cursht.Cells(a, b) = thisisstupid
a = a + 1
If a > 250 Then b = b + 1
If a > 250 Then a = 1
Loop

 

[xenou]

'prevmon, curmon, and dSheet have already been defined
[B][COLOR="#FF0000"]Set prevxl = CreateObject("Excel.Application")[/COLOR][/B]
Set prevwk = prevxl.Workbooks.Open(prevmon)
[B][COLOR="#FF0000"]Set curxl = CreateObject("Excel.Application")[/COLOR][/B]
Set curwk = curxl.Workbooks.Open(curmon)
Set prevsht = prevwk.Sheets(dSheet)
Set cursht = curwk.Sheets(dSheet)

You are creating two excel applications which is a mistake. Just like in real life - you wouldn't open Excel two times. You would open excel one time and then with that application open two workbooks workbooks.

There are more than one way to go about copying sheet data but this would be how I most commonly do this (in Excel directly):

Sub foo()

Const PASTE_VALUES_AND_NUMBER_FORMATS As Integer = 12
Dim wbSrc As Workbook
Dim wbDest As Workbook

Set wbSrc = Workbooks("Book1.xlsm")
Set wbDest = Workbooks("Book2.xlsm")

wbSrc.Sheets(1).Cells.Copy
wbDest.Sheets(1).Cells(1).PasteSpecial (PASTE_VALUES_AND_NUMBER_FORMATS)
Application.CutCopyMode = False

End Sub

Just adapt for your situation.

 

[CPGDeveloper]

That seemed to be the issue. Thanks!

 

[xenou]

Cool.
Also, as a rule, don't forget when done to close workbooks and Quit the app.