Solved

Copy data to open workbook from two others in the background.

Posted on 2010-08-24
3
230 Views
Last Modified: 2012-05-10
The attached code gets the datafrom the two other workbooks to the open workbook, but it doesn't do it in the background. How do I get the code to keep the other two workbooks from opening up to the screen, notifying the user that there is a lot of data on the clipboard when the file closes, etc. If you see any other things that I could clean up, let me know.
Sub getDATA()



Dim sourceSAS, sorceFN, targetSAS, targetFN, finalSAS, finalFN



'perform saveAS for finalSAS

MsgBox ("Select location and enter a name for the finalSAS file.")

finalSAS = Application.GetSaveAsFilename _

(Title:="Please choose a location and name for the final SAS file.", _

FileFilter:="Excel Files *.xls (*.xls),")



    If finalSAS = False Then

        MsgBox "No file specified.", vbExclamation, "No file selected!!!"

        Exit Sub

    End If

MsgBox ("finalSAS: " & finalSAS)

ActiveWorkbook.SaveAs Filename:=finalSAS

finalFN = Application.ActiveWorkbook.Name



'get sourceSAS location

MsgBox ("Select the source SAS. This is the older SAS most likely filled out by the SAs.")

sourceSAS = Application.GetOpenFilename _

(Title:="Please choose the source SAS xml file to import", _

FileFilter:="Excel Files *.xml (*.xml),")



    If sourceSAS = False Then

        MsgBox "No file specified.", vbExclamation, "No file selected!!!"

        Exit Sub

    End If



MsgBox ("sourceSAS: " & sourceSAS)



'get targetSAS location

MsgBox ("Select the target SAS. This is the new SAS. You want to populate this one with the comments from the source SAS.")

targetSAS = Application.GetOpenFilename _

(Title:="Please choose the target SAS xml file to import", _

FileFilter:="Excel Files *.xml (*.xml),")



    If targetSAS = False Then

        MsgBox "No file specified.", vbExclamation, "No file selected!!!"

        Exit Sub

    End If

MsgBox ("targetSAS: " & targetSAS)



ApplicationScreenUpdating = False

ApplicationDisplayAlerts = False



'copy from sourceSAS

Workbooks.Open (sourceSAS)

ApplicationScreenUpdating = False

ApplicationDisplayAlerts = False

sorceFN = Application.ActiveWorkbook.Name

Sheets("SAS").Activate

Cells.Select

Selection.Copy

Workbooks(finalFN).Activate

Sheets("sourceSAS").Activate

Cells.Select

ActiveSheet.Paste

Workbooks(sorceFN).Activate

ApplicationCutCopyMode = False

Workbooks(sorceFN).Close False



'copy from targetSAS

Workbooks.Open (targetSAS)

ApplicationScreenUpdating = False

ApplicationDisplayAlerts = False

targetFN = Application.ActiveWorkbook.Name

Sheets("SAS").Activate

Cells.Select

Selection.Copy

Workbooks(finalFN).Activate

Sheets("targetSAS").Activate

Cells.Select

ActiveSheet.Paste

Workbooks(targetFN).Activate

ApplicationCutCopyMode = False

Workbooks(targetFN).Close False



ApplicationScreenUpdating = True

ApplicationDisplayAlerts = True





End Sub

Open in new window

0
Comment
Question by:brukilla
3 Comments
 
LVL 18

Accepted Solution

by:
Jerry Miller earned 500 total points
ID: 33517811
I use this to hide Excel workbooks. I place the 'Visible = False' section after the Workbook.Open event.


In the declarations section:

Dim objXls As Excel.Application



Set objXls = CreateObject("Excel.Application")





objXls.Visible = False

objXls.Application.DisplayAlerts = False



<your code here>



objXls.Application.DisplayAlerts = True

objXls.Visible = True

Set objXls = nothing

Open in new window

0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33520942
you can also use:
application.cutcopymode = false
before closing the file.  this will empty the clipboard and won't prompt the user to clear it.
0
 

Author Closing Comment

by:brukilla
ID: 33522390
Did what I wanted.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now