Paste values from 2 fields to an Excel sheet

  • Thread starter Thread starter Bill
  • Start date Start date
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
 
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
 
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...
 
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).
 
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...
 
Back
Top