Copy Data from one Excel Spreadsheet to another without being able to refresh using a VB Module

I have VB Code that copies data from one spreadsheet to another but I want to copy the data only not the query (  I want to create a new spreadsheet without the "Refresh" capability.

Does anyone know how to do this using VB Code.  I can manually do it, but would like to code it so it can be put in an automated process if possible
moriniaAdvanced Analytics AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RobSampsonCommented:
Hi, try this macro.  Specify your second workbook to copy to, and change sheet names if required.

Sub CopyValuesOnly()
    Set wb1 = ActiveWorkbook
    strWorkbook2 = "C:\TEMP\Temp\Test script\Excel\Test2.xls"
    Set wb2 = Workbooks.Open(Filename:=strWorkbook2)
    Application.CutCopyMode = False
    wb1.Sheets("Sheet1").Cells.Copy
    wb2.Sheets("Sheet1").Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    wb2.Sheets("Sheet1").Range("A1").Select
    'wb1.Sheets("Sheet1").Range("A1").Select
    wb2.Save
    wb2.Close
    MsgBox "Data copied"
End Sub


Regards,

Rob.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
moriniaAdvanced Analytics AnalystAuthor Commented:
Rob,

The solution worked perfectly.  Is there a way to execute this outside of Excel using a VB Script?
0
moriniaAdvanced Analytics AnalystAuthor Commented:
I would like to award the points
0
RobSampsonCommented:
Yes, you can use this outside Excel. Put this in a VBS file:

'====================
Set objExcel = CreateObject("Excel.Application")
Const xlPasteValues = -4163
Const xlNone = -4142
strWorkbook1 = "C:\TEMP\Temp\Test script\Excel\Test.xls"
strWorkbook2 = "C:\TEMP\Temp\Test script\Excel\Test2.xls"
Set wb1 = objExcel.Workbooks.Open(strWorkbook1, False, False)
Set wb2 = objExcel.Workbooks.Open(strWorkbook2, False, False)
objExcel.Visible = False
'objExcel.CutCopyMode = False
wb1.Sheets("Sheet1").Cells.Copy
wb2.Sheets("Sheet1").Range("A1").Select
objExcel.Selection.PasteSpecial xlPasteValues, xlNone, False, False
wb2.Sheets("Sheet1").Range("A1").Select
'wb1.Sheets("Sheet1").Range("A1").Select
wb2.Save
wb2.Close
wb1.Close
objExcel.Quit
Set objExcel = Nothing
MsgBox "Data copied"
'====================

Regards,

Rob.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.