VBA Copy to Unopened Workbook

[Luke777]

Hi all,

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

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

 

[JoeMo]

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

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]

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]

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]

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]

Thanks for the replies - looks like I might need to rethink my methodology for this one

 

[jolivanes]

It will be closed before they know what happened

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]

It will be closed before they know what happened

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]

Or are book4/book3 the same thing?

 

[Luke777]

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.