Paste values from 2 fields to an Excel sheet

B

Bill

Greetings,
I get a run-time error of 424 at line
" Me![Density].value.copy"
In the following

Private Function Fred()

Dim strA As String
Dim strB As String
Dim rng1 As Excel.Range
Dim rng2 As Excel.Range
Dim oSheet As Excel.Worksheet


strA = Me![Density]
strB = Me![Rate]


If Not IsNull(Me![Density]) And Not IsNull(Me![Rate]) Then

ExcelRunning = IsExcelRunning
If Not ExcelRunning Then
Set xlapp = CreateObject("Excel.Application")
Else
Set xlapp = GetObject(, "Excel.Application")
End If
Set xlbook = xlapp.Workbooks.Open("C:\Database\ltd calcs sheet.xls")
Set oSheet = xlapp.Worksheets("Quick RV Sizing")
xlapp.Visible = True

Me![Density].Value.Copy
Set rng1 = oSheet.Range("b3")
oSheet("Quick RV Sizing").Range(rng1.Address).PasteSpecial
Paste:=xlPasteValues
Me![Rate].Value.Copy
Set rng2 = oSheet.Range("b4")
oSheet("Quick RV Sizing").Range(rng1.Address).PasteSpecial
Paste:=xlPasteValues
Else
'do nothing
End If


End Function

My hope - not working at present - is to take the vlaues in the fields of
the current form and paste them into cells in aN Excel worksheet before it
opens.
(The spreadsheet is a calculation).

Is there a way to do this - that is simple - and works?.

Thanx
Best regards
Bill
 
S

Stuart McCall

Bill said:
Greetings,
I get a run-time error of 424 at line
" Me![Density].value.copy"
In the following

Private Function Fred()

Dim strA As String
Dim strB As String
Dim rng1 As Excel.Range
Dim rng2 As Excel.Range
Dim oSheet As Excel.Worksheet


strA = Me![Density]
strB = Me![Rate]


If Not IsNull(Me![Density]) And Not IsNull(Me![Rate]) Then

ExcelRunning = IsExcelRunning
If Not ExcelRunning Then
Set xlapp = CreateObject("Excel.Application")
Else
Set xlapp = GetObject(, "Excel.Application")
End If
Set xlbook = xlapp.Workbooks.Open("C:\Database\ltd calcs
sheet.xls")
Set oSheet = xlapp.Worksheets("Quick RV Sizing")
xlapp.Visible = True

Me![Density].Value.Copy
Set rng1 = oSheet.Range("b3")
oSheet("Quick RV Sizing").Range(rng1.Address).PasteSpecial
Paste:=xlPasteValues
Me![Rate].Value.Copy
Set rng2 = oSheet.Range("b4")
oSheet("Quick RV Sizing").Range(rng1.Address).PasteSpecial
Paste:=xlPasteValues
Else
'do nothing
End If


End Function

My hope - not working at present - is to take the vlaues in the fields of
the current form and paste them into cells in aN Excel worksheet before it
opens.
(The spreadsheet is a calculation).

Is there a way to do this - that is simple - and works?.

Thanx
Best regards
Bill
Click to expand...

I'm no Excel VBA expert, but it seems to me that you don't need to copy &
paste at all, just assign the value directly, something like:

oSheet("Quick RV Sizing").Range(rng1.Address).Text = Me![Density].Value
 
B

Bill

Hi Stuart,
Thankx,

Tried the
"Set rng1 = oSheet.Range("b3")
oSheet("Quick RV Sizing").Range(rng1.Address).Text =
Me![Density].Value"

and got an error 438
Object doesn't support this property or method for line "
"oSheet("Quick RV Sizing").Range(rng1.Address).Text = Me![Density].Value

So not sue what to try next.
Thanx
Best Regards
Bill



Stuart McCall said:
Bill said:
Greetings,
I get a run-time error of 424 at line
" Me![Density].value.copy"
In the following

Private Function Fred()

Dim strA As String
Dim strB As String
Dim rng1 As Excel.Range
Dim rng2 As Excel.Range
Dim oSheet As Excel.Worksheet


strA = Me![Density]
strB = Me![Rate]


If Not IsNull(Me![Density]) And Not IsNull(Me![Rate]) Then

ExcelRunning = IsExcelRunning
If Not ExcelRunning Then
Set xlapp = CreateObject("Excel.Application")
Else
Set xlapp = GetObject(, "Excel.Application")
End If
Set xlbook = xlapp.Workbooks.Open("C:\Database\ltd calcs
sheet.xls")
Set oSheet = xlapp.Worksheets("Quick RV Sizing")
xlapp.Visible = True

Me![Density].Value.Copy
Set rng1 = oSheet.Range("b3")
oSheet("Quick RV Sizing").Range(rng1.Address).PasteSpecial
Paste:=xlPasteValues
Me![Rate].Value.Copy
Set rng2 = oSheet.Range("b4")
oSheet("Quick RV Sizing").Range(rng1.Address).PasteSpecial
Paste:=xlPasteValues
Else
'do nothing
End If


End Function

My hope - not working at present - is to take the vlaues in the fields of
the current form and paste them into cells in aN Excel worksheet before it
opens.
(The spreadsheet is a calculation).

Is there a way to do this - that is simple - and works?.

Thanx
Best regards
Bill
Click to expand...

I'm no Excel VBA expert, but it seems to me that you don't need to copy &
paste at all, just assign the value directly, something like:

oSheet("Quick RV Sizing").Range(rng1.Address).Text = Me![Density].Value
Click to expand...
 
S

Stuart McCall

Bill said:
Hi Stuart,
Thankx,

Tried the
"Set rng1 = oSheet.Range("b3")
oSheet("Quick RV Sizing").Range(rng1.Address).Text =
Me![Density].Value"

and got an error 438
Object doesn't support this property or method for line "
"oSheet("Quick RV Sizing").Range(rng1.Address).Text = Me![Density].Value

So not sue what to try next.
Thanx
Best Regards
Bill



Stuart McCall said:
Bill said:
Greetings,
I get a run-time error of 424 at line
" Me![Density].value.copy"
In the following

Private Function Fred()

Dim strA As String
Dim strB As String
Dim rng1 As Excel.Range
Dim rng2 As Excel.Range
Dim oSheet As Excel.Worksheet


strA = Me![Density]
strB = Me![Rate]


If Not IsNull(Me![Density]) And Not IsNull(Me![Rate]) Then

ExcelRunning = IsExcelRunning
If Not ExcelRunning Then
Set xlapp = CreateObject("Excel.Application")
Else
Set xlapp = GetObject(, "Excel.Application")
End If
Set xlbook = xlapp.Workbooks.Open("C:\Database\ltd calcs
sheet.xls")
Set oSheet = xlapp.Worksheets("Quick RV Sizing")
xlapp.Visible = True

Me![Density].Value.Copy
Set rng1 = oSheet.Range("b3")
oSheet("Quick RV Sizing").Range(rng1.Address).PasteSpecial
Paste:=xlPasteValues
Me![Rate].Value.Copy
Set rng2 = oSheet.Range("b4")
oSheet("Quick RV Sizing").Range(rng1.Address).PasteSpecial
Paste:=xlPasteValues
Else
'do nothing
End If


End Function

My hope - not working at present - is to take the vlaues in the fields
of
the current form and paste them into cells in aN Excel worksheet before
it
opens.
(The spreadsheet is a calculation).

Is there a way to do this - that is simple - and works?.

Thanx
Best regards
Bill
Click to expand...

I'm no Excel VBA expert, but it seems to me that you don't need to copy &
paste at all, just assign the value directly, something like:

oSheet("Quick RV Sizing").Range(rng1.Address).Text = Me![Density].Value
Click to expand...
Click to expand...

Well maybe the .Text property wasn't the correct property to use, but the
gist of what I said still holds true. You need to find out how to assign
something to a Range (I think).
 
B

Bill

Thanks Stuart,
I've tried .value instead of .text with the same result.

Assigning to a range - will explore.
Cheers
Best Regards
Bill


Stuart McCall said:
Bill said:
Hi Stuart,
Thankx,

Tried the
"Set rng1 = oSheet.Range("b3")
oSheet("Quick RV Sizing").Range(rng1.Address).Text =
Me![Density].Value"

and got an error 438
Object doesn't support this property or method for line "
"oSheet("Quick RV Sizing").Range(rng1.Address).Text = Me![Density].Value

So not sue what to try next.
Thanx
Best Regards
Bill



Stuart McCall said:
Greetings,
I get a run-time error of 424 at line
" Me![Density].value.copy"
In the following

Private Function Fred()

Dim strA As String
Dim strB As String
Dim rng1 As Excel.Range
Dim rng2 As Excel.Range
Dim oSheet As Excel.Worksheet


strA = Me![Density]
strB = Me![Rate]


If Not IsNull(Me![Density]) And Not IsNull(Me![Rate]) Then

ExcelRunning = IsExcelRunning
If Not ExcelRunning Then
Set xlapp = CreateObject("Excel.Application")
Else
Set xlapp = GetObject(, "Excel.Application")
End If
Set xlbook = xlapp.Workbooks.Open("C:\Database\ltd calcs
sheet.xls")
Set oSheet = xlapp.Worksheets("Quick RV Sizing")
xlapp.Visible = True

Me![Density].Value.Copy
Set rng1 = oSheet.Range("b3")
oSheet("Quick RV Sizing").Range(rng1.Address).PasteSpecial
Paste:=xlPasteValues
Me![Rate].Value.Copy
Set rng2 = oSheet.Range("b4")
oSheet("Quick RV Sizing").Range(rng1.Address).PasteSpecial
Paste:=xlPasteValues
Else
'do nothing
End If


End Function

My hope - not working at present - is to take the vlaues in the fields
of
the current form and paste them into cells in aN Excel worksheet before
it
opens.
(The spreadsheet is a calculation).

Is there a way to do this - that is simple - and works?.

Thanx
Best regards
Bill

I'm no Excel VBA expert, but it seems to me that you don't need to copy &
paste at all, just assign the value directly, something like:

oSheet("Quick RV Sizing").Range(rng1.Address).Text = Me![Density].Value
Click to expand...
Click to expand...

Well maybe the .Text property wasn't the correct property to use, but the
gist of what I said still holds true. You need to find out how to assign
something to a Range (I think).
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Two Q's: Trouble Closing Excel & run-Time 462 5
Automate opening a 2nd xl book and copy ranges to other xlbook 1
Export Access to Excel via VBA -- 255 Character Memo Problem 1
Add sheet 2
CheckBox Code to copy & paste range 0
Working with Excel objects from Access 2
Paste query results to Excel 0
Import rates from excel via macro 6

Top