Solved

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

Posted on 2010-08-24
3
243 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

947 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

21 Experts available now in Live!

Get 1:1 Help Now