Solved

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

Posted on 2010-08-24
3
270 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
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 …

726 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