Macro for copy pasting without opening the source

Umasriram2 · March 28th, 2008, 08:56 AM

Hi All, I'm looking for a macro that can copy paste values from one file to another. Since I've multiple source files, I'm looking for a way to copy paste without opening all the files. Fyi...All the source files are saved in the same location as the output file. Kindly help me with the right command.Thanks!

tstav · March 28th, 2008, 10:53 AM

The general idea is that you have to be specific about which cell addresses you want to copy from the closed files.
The code to use is:

Code:

'for a single cell
Range("A1").Value = "='C:\MyFolder\[1.xls]Sheet1'!A15"
'for more than one cells, always copy on a row by row basis
Range("A2:B2").Value = "='C:\MyFolder\[1.xls]Sheet1'!A15:B15"

The 1.xls is your closed file, the Sheet1 is the name of the Sheet with the data and the C:\MyFolder is its path.
The documentation I have come across about this subject is extremely poor.

Umasriram2 · March 31st, 2008, 06:41 AM

Hi, Amazing....Thanks a lot .That works great.One more thing I'm curious about...This seems to copy the cells as links rather than values. Is there a way I can paste these as values rather than link? Many Thanks again!

tstav · March 31st, 2008, 09:34 AM

Quote:

quote:Originally posted by Umasriram2
Is there a way I can paste these as values rather than links?

Yes there is.
Use this before copying values

Code:

ThisWorkbook.SaveLinkValues = False

and after copying is done you can reinstate the SaveLinkValues to True (if, for whatever reason, you might need to do so).

Umasriram2 · April 2nd, 2008, 09:14 AM

Many thanks...It helped. But I see the blank rows showing 0's when pasted as values. How do I tackle this...Thanks

tstav · April 2nd, 2008, 10:29 AM

Apologies... I should've mentioned the zeros right from the start.
To avoid them you'll have to do two things.
1. Copy cells one cell at a time.
2. Add the following snippet to the Worksheet_Change event.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
If Application.EnableEvents = True Then
   Application.EnableEvents = False
   If Target.Value = 0 Then
      Target.Value = ""
   End If
End If
ErrorHandler:
Application.EnableEvents = True
End Sub

The above event code will not work for ranges larger than one cell. Target is always one cell.
P.S. Real zeros being copied over will also be turned to blanks. No way to fix that.

Umasriram2 · April 9th, 2008, 02:27 AM

Got it..THANK YOU!