Need help fixing xlsx copying from on sheet to another one when a form's control pick (1 Viewer)

Punice

Registered User.
Local time
Today, 07:35
Joined
May 10, 2010
Messages
135
I have a control on a customer's form that copies a template xlsx file and fills it with customer name, address, etc. While it is open, I want to copy a cell's value from one xlsx file and paste it into a cell on the open xlsx file that I just created.
After trying various suggestions seen on the 'net for 12 weeks, I am asking this great forum, once again, for help. Did learn a lot during the time spent, though.
Here is my code:

Private Sub SOMCreate_Label_Click()
Dim appExcel As Excel.Application 'For creating the destination file
Dim lngLastDataRow As Long 'That code is not shown, here.
Dim Folder_Path As String
Dim strFolder_PathNew As String
Dim strFolder_PathSrc As String

'There is code here that creates the destination file: L_Name SOM.xlsx , ' from a 'customers form' in my db, & leaves it open.
'I have a source file: L_Name Roof.xlsx' that contains a cell with the ' "matlcost" value.
'I want to copy the 'matlcost' value in cell "M59" of the source file to cell ' "E14" on the open destination file.
'The following code is 1 of the zillion versions that I tried that didn't work.

Dim srce As Workbook 'source
Dim dstn As Workbook 'destination

strFolder_PathSrc = strFolder_Path & "" & Me.[L_Name] & " Roof" &_ ".xlsx"
strFolder_PathNew = strFolder_Path & "" & Me.[L_Name] & " SOM" &_ ".xlsx"
Set srce = Workbooks.Open(strFolder_PathSrc) 'Opens the source file

'Copies from the source worksheet to the destination worksheet.
Worksheets("Sheet1").Range("M59").Value = srce.Worksheets_("Sheet1").Range("E14").Value

'Closes the source file, leaving the destination file open with the 'matlcost ' ' value added.
srce.Close False
.End Sub
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:35
Joined
Sep 21, 2011
Messages
14,038
I think you should show all of your code?

I would have also thought that you would need the Excel object reference included in the statements.?

I would also try recording a macro to do what you want, then adjust to suit.?
 

Punice

Registered User.
Local time
Today, 07:35
Joined
May 10, 2010
Messages
135
The code that I didn't show creates the ". . . . .\L_Name SOM.xlsx" file and keeps it open. Other than that, it's not related to what I'm trying to do with the code that I did include with my submission.

I tried numerous macros that were available on the 'net, as I stated, and adjusted them to fit my application. Every one of them created various error messages when I ran them.

I didn't understand what they meant and spent a lot of time trying to do that without solving my problem.

It seems that every time I tried to 'improve' the performance of my DB and write some code, I encounter a problem. It does force me to do research from which I benefit. So, I guess that's a good thing, but after trying some of the macros that are posted on the 'net by self appointed authorities and getting nowhere after twelve weeks of wall-banging, I get frustrated.

:banghead:Won't give up though. I believe the old adage that if you play in the streets long enough, you're bound to get hit by a car.

Thanks for your suggestions. I do appreciate your attempt to help.
 

GinaWhipp

AWF VIP
Local time
Today, 07:35
Joined
Jun 21, 2011
Messages
5,901
Hmm, well I'm curious HOW this line knows to copy from old worksheet to new worksheet...

Code:
Worksheets("Sheet1").Range("M59").Value = srce.Worksheets_("Sheet1").Range("E14").Value

You identified the new one with srce. but there is nothing to identify the old one. Is this all the code? If not, please post the entire section. I know you say it's only creating the new worksheet but seems like it must doing something more.
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:35
Joined
Sep 21, 2011
Messages
14,038
If you were to follow the exact process that you want to happen in Excel with the macro recorder, that will give you a very good start.

You are not going to find anything on the net that will just paste in and work. You need to find a good match and alter to fit.

The macro recorder will be as close as you will get to what you want, but you will still need to alter it.

For clarity, is this in Access or Excel?

The code that I didn't show creates the ". . . . .\L_Name SOM.xlsx" file and keeps it open. Other than that, it's not related to what I'm trying to do with the code that I did include with my submission.

I tried numerous macros that were available on the 'net, as I stated, and adjusted them to fit my application. Every one of them created various error messages when I ran them.

I didn't understand what they meant and spent a lot of time trying to do that without solving my problem.

It seems that every time I tried to 'improve' the performance of my DB and write some code, I encounter a problem. It does force me to do research from which I benefit. So, I guess that's a good thing, but after trying some of the macros that are posted on the 'net by self appointed authorities and getting nowhere after twelve weeks of wall-banging, I get frustrated.

:banghead:Won't give up though. I believe the old adage that if you play in the streets long enough, you're bound to get hit by a car.

Thanks for your suggestions. I do appreciate your attempt to help.
 

Punice

Registered User.
Local time
Today, 07:35
Joined
May 10, 2010
Messages
135
Adding "dstn", as shown below, didn't work, either.

dstn.Worksheets("Sheet1").Range("M59").Value = srce.Worksheets("Sheet1").Range("E14").Value

BTW & for what it's worth, I am able to manually entered a value in the "E14" cell of the destination xlsx file that was created, initially, which proved that it is a read-only file. I was thinking about whether or not it was a read-only last night around 3 A.M. while attempting to sleep.
 

Punice

Registered User.
Local time
Today, 07:35
Joined
May 10, 2010
Messages
135
I should have said this in my initial description:

I have a control on a customer's form, in an Access 2007 DB, that copies a template Excel file and fills it with customer name, address, etc. While it is open, I want to copy a cell "M59"s value from one xlsx file and paste it into cell"E14" on the open xlsx file that was just created by the un-shown code of the current sub-routine: Private Sub SOMCreate_Label_Click().
BTW, should the sub be 'Private or Public'?
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:35
Joined
Sep 21, 2011
Messages
14,038
Your code has the cell being copied in the opposite direction?, E14 to M59 ?

Is it actually being copied, but E14 is empty?
What happens if you wlak through the code with the debug window and F8.?

That is how I always try and find errors like this.


I should have said this in my initial description:

I have a control on a customer's form, in an Access 2007 DB, that copies a template Excel file and fills it with customer name, address, etc. While it is open, I want to copy a cell "M59"s value from one xlsx file and paste it into cell"E14" on the open xlsx file that was just created by the un-shown code of the current sub-routine: Private Sub SOMCreate_Label_Click().
BTW, should the sub be 'Private or Public'?
 

Users who are viewing this thread

Top Bottom