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]

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.

Just in case you missed it - the code you are exploring opens the workbook you said you didn't want opened, so its visible. Should the user hit esc or ctrl+break during execution, or some other unexpected event causes a break, the workbook will be exposed.

 

[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

Okay, so I've managed to get this code working! Had to change a few of the Cells() values which caught me out, but it works PERFECTLY so thanks for that! I just need to add a bit of code that stops it from overwriting itself every time I add new data to the destination workbook - but I *think* I should be able to work that one out... possibly lol.

Thanks again to both of you that have helped here

 

[jolivanes]

"C:\Users\Luke\Desktop" is the path to your desktop. Verify and change if required.
"Desktop Book3.xlsm" is a workbook saved to your desktop. If a different name, change wherever needed.
"Desktop Book4.xlsm" is the workbook with the code in it and is housed in another folder somewhere.
"sh1" and "sh2" are worksheets in "Desktop Book4.xlsm" and "Desktop Book3.xlsm" respectively.
"sh1.Cells(8, 11).Resize(13)" is Range("K8:K22") in sh1. It is the data that will be transferred (copy if you will) to sh2.
"Cells(10, 4)" is the top cell in sh2 where the data from sh1 will start. It needs to be resized to the same size as the data from sh1.
The naming is confusing. I should have made that a little more self explanatory.

 

[Luke777]

Just in case you missed it - the code you are exploring opens the workbook you said you didn't want opened, so its visible. Should the user hit esc or ctrl+break during execution, or some other unexpected event causes a break, the workbook will be exposed.

Yeah, thank you for pointing that one out, I realized, but it's still helpful for anyone else that happens along this thread. I may end up having to scrap this method entirely - but as far as I can tell, all variations of what I'm trying to do have a flaw one way or another. Thanks again though!

 

[TotallyConfused]

Hello Luke
While the workbook would still be opened, it might help if you placed the following line of code at the beginning (before anything happens) of the macro. This will freeze the screen to whatever is already there.
Application.ScreenUpdating = False
Now at the end after everything is completed, place the following line of code. This will unfreeze the screen.
Application.ScreenUpdating = True
To reduce the time available for anyone to break the code operation, as much as possible, I'd place any code OUTSIDE of the 'Open' and the 'Close' commands. In other words, get that file closed again as quickly as possible. I hope this will be of some help.

TotallyConfused