VBA Copy to Unopened Workbook

Luke777

New Member
Joined
Aug 10, 2020
Messages
19
Office Version
  1. 2007
Platform
  1. Windows
Hi all,

I've recently written some code, the crux of which (there's a lot omitted here) looks like this...

VBA Code:
Sub CopySend()

'Main: Copy and paste to database code'
Range("B2:M2").Copy Workbooks("Incident Log - Database.xlsm").Worksheets("All Incidents").Range("B" & Rows.Count).End(xlUp).Offset(1, 0)

End Sub

Basically, a button is pressed on a sheet>range B2:M2 is then copied over to the next empty row in "Incident Log - Database.xlsm". That part works a wonderfully, but being very new to this, I was a bit disappointed when I realized the destination workbook needs to be open for this bit of code to function correctly.

Is there a way to make it work without the destination workbook being open? I cannot allow users to open the Database file for GDPR reasons.

Thanks everyone :)
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,326
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Basically, a button is pressed on a sheet>range B2:M2 is then copied over to the next empty row in "Incident Log - Database.xlsm". That part works a wonderfully, but being very new to this, I was a bit disappointed when I realized the destination workbook needs to be open for this bit of code to function correctly.

Is there a way to make it work without the destination workbook being open? I cannot allow users to open the Database file for GDPR reasons.

Thanks everyone :)
Not sure if that can be done, but here's an alternative I used several years ago that may be acceptable. Basically, The GetObject function is used to open the destination workbook, hidden so the user is unaware it's open. I used this to access information in the workbook, but I imagine that can be reversed to paste to the workbook. Below is the basic framework, but you will have to modify the file path and filename to use it. The workbook is closed and saved before the code ends so, once again, the user should never see it. Try it on a copy of your workbook.

In your main sub you would have something like this:
Range("B2:M2").Copy
Call PutDataIntoClosedWorkBook
'rest of code
VBA Code:
Sub PutDataIntoClosedWorkBook()
Dim fPath As String, fName As String
fPath = "C:\Users\Joe\Desktop" & Application.PathSeparator 'Change path to suit
fName = "Book1.xlsm"  'Change name to suit
With GetObject(fPath & fName)
    With Worksheets("All Incidents").Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
        .PasteSpecial Paste:=xlPasteAll
    End With
    .Close SaveChanges:=True
End With
End Sub
 

Luke777

New Member
Joined
Aug 10, 2020
Messages
19
Office Version
  1. 2007
Platform
  1. Windows
Not sure why but I'm getting the following message when I try to run the code "Run-time error'432: File name or class name not found during Automation Operation".

I can't see anything wrong with the workbook name or file path... but it isn't happy with them for one reason or another. I'll let you know if I solve it
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,326
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Not sure why but I'm getting the following message when I try to run the code "Run-time error'432: File name or class name not found during Automation Operation".

I can't see anything wrong with the workbook name or file path... but it isn't happy with them for one reason or another. I'll let you know if I solve it
Can you share the file name and path name you used? Perhaps you had your own path separator at the end of the file path you used and left the Application.PathSeparator in place which would add an unwanted second separator?
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,326
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

I used this to access information in the workbook, but I imagine that can be reversed to paste to the workbook.

I just ran a quick test and my earlier assertion (above) is not correct. It appears that the workbook must be made visible for the pastespecial to work. Obviously, that is problematic given your security concern (or whatever "GDPR" is). Sorry about that.
 

Luke777

New Member
Joined
Aug 10, 2020
Messages
19
Office Version
  1. 2007
Platform
  1. Windows
Thanks for the replies - looks like I might need to rethink my methodology for this one
 

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,658
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

It will be closed before they know what happened
Code:
Sub PutDataIntoClosedWorkBook()
Dim fPath As String, fName As String
Dim wb1 As Workbook, wb2 As Workbook, sh1 As Worksheet, sh2 As Worksheet
Set wb1 = Workbooks("Desktop Book4.xlsm")  'Change name to suit
Set sh1 = Workbooks("Desktop Book4.xlsm").Worksheets("Sheet1")  'Change name to suit
fPath = "C:\Users\Luke\Desktop" & Application.PathSeparator 'Change path to suit
fName = "Desktop Book3.xlsm"  'Change name to suit
Workbooks.Open (fPath & fName)
Windows("Desktop Book4.xlsm").Activate  'Change name to suit
Set wb2 = Workbooks("Desktop Book3.xlsm")  'Change name to suit
Set sh2 = Workbooks("Desktop Book3.xlsm").Worksheets("Sheet1")  'Change name to suit
    sh2.Cells(10, 4).Resize(13).Value = sh1.Cells(8, 11).Resize(13).Value
wb2.Close SaveChanges:=True
End Sub
 

Luke777

New Member
Joined
Aug 10, 2020
Messages
19
Office Version
  1. 2007
Platform
  1. Windows
It will be closed before they know what happened
Code:
Sub PutDataIntoClosedWorkBook()
Dim fPath As String, fName As String
Dim wb1 As Workbook, wb2 As Workbook, sh1 As Worksheet, sh2 As Worksheet
Set wb1 = Workbooks("Desktop Book4.xlsm")  'Change name to suit
Set sh1 = Workbooks("Desktop Book4.xlsm").Worksheets("Sheet1")  'Change name to suit
fPath = "C:\Users\Luke\Desktop" & Application.PathSeparator 'Change path to suit
fName = "Desktop Book3.xlsm"  'Change name to suit
Workbooks.Open (fPath & fName)
Windows("Desktop Book4.xlsm").Activate  'Change name to suit
Set wb2 = Workbooks("Desktop Book3.xlsm")  'Change name to suit
Set sh2 = Workbooks("Desktop Book3.xlsm").Worksheets("Sheet1")  'Change name to suit
    sh2.Cells(10, 4).Resize(13).Value = sh1.Cells(8, 11).Resize(13).Value
wb2.Close SaveChanges:=True
End Sub
Thanks for the reply!

I'm a little confused about book4 vs book3 in your example code - Am I right in thinking that book4 is the 'destination' workbook and book3 is the one from where information is being copied?
 

Luke777

New Member
Joined
Aug 10, 2020
Messages
19
Office Version
  1. 2007
Platform
  1. Windows
Or are book4/book3 the same thing?
 

Luke777

New Member
Joined
Aug 10, 2020
Messages
19
Office Version
  1. 2007
Platform
  1. Windows
Sorry for the repeated replies - I got the code to do something. It's too fast to see what's going on (as you mentioned) which is good, but unfortunately no data is being transferred between the two workbooks at present. I'll do some more tinkering and report back.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,493
Messages
5,548,370
Members
410,828
Latest member
A9Bosv3
Top