Copy an excel range and paste special Values (Excel/Access VBA) Hi, I\'ve writte

Question

Copy an excel range and paste special Values (Excel/Access VBA)

Hi,

I've written some code in Access 2013 to export some tables to an Excel Spreadsheet and generate 1,000,000 random samples per sheet.

Each time a new calculation is performed, all the RANDBETWEEN formulas recalculate, causing the code to run very slowly.

I tried to copy and paste special Values, but it keeps popping up with warnings about the area not being the same.

Dim xl as Object

Set xl = CreateObject("Excel.Application")

'copy and paste values to prevent recalculation

With xl

        .Range(SheetName & "!C50:CX10049").copy
        .Range(SheetName & "!C50:CX10049").select
        .Range(SheetName & "!C50:CX10049").PasteSpecial Paste:=xlPasteValues

End with

Any ideas?

Thanks !!

Explanation / Answer

Hi...

Sorry if I confused you - these two lines

Set wb = xl.workbooks.Open("Path and filename")

Set ws = wb.worksheets("Sheet Name")

Should actually reference the names of those things, not the phrases, e.g.

Set wb= xl.workbooks.open("C:oldernameoldernamesomefile.xlsx")

where foldername and somefile are the actual names of those things.

Same for the sheet name:

Set ws = wb.worksheets("Sheet1")

This code works for me to open a file from within Access, copy a range of cells from one sheet and paste them into another sheet. Note that if you actually want to see the workbook, somewhere after creating the xl object, you need a

xl.Visible = True

statement, otherwise it remains invisible.

Sub OpenExcelWB()
Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
  
Set xl = CreateObject("Excel.Application").
Set wb = xl.Workbooks.Open("C:usersjlathamdesktopug13-nov14.xls").
Set ws = wb.Worksheets(""FNB-399"")
With ws.
    .Range("C3:C50").Copy
    'copy to a different sheet into column A at A1
    wb.Worksheets("Sheet1").Range("A1").PasteSpecial xlPasteAll
End With
'shut everything down
Set ws = Nothing
'close the workbook and save changes
wb.Close True
Set wb = Nothing
'quit excel
xl.Quit
Set xl = Nothing
End Sub

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.