Hello,
I know there are a number of threads dealing with this topic, but everything I have looked at seems to have the issue resolved at 'set numberformat', and that is not solving my problem, so I decided to post a new thread.
I am working in access and excel 2003. I am using copyfromrecordset to paste some query results into excel and then after that, I want to insert sum formulas below the pasted range- so I need the pasted values to be in currency or number format.
Here is where the problem is: I get the pasting and formula set up all happen perfectly well, but the sums are all 0, because the pasted range is formatted as text or general by default.
I tried using range(...).numberformat= "Number"
and range(...).numberformat= "$#.##" (what I really need is the currency format) after the data are already pasted. That did nothing-the range is still text. I also tried setting the right number formatting to the sheet before the data are pasted- no luck there either.
Generally, when I run into trouble with changing the format from text to something else in excel, I manually do text to columns on the selected columns and that changes the format to what I have just set. However, doing this manually is not an option for me- just too many workbooks with too many sheets.
Should I just try to do text to columns on the selected range programatically? I am willing to try that, but somehow I am sensing that this is not the most elegant- or even correct - solution to this. I feel like I should just be able to resolve things by simply setting the numberformat. Please post any suggestions. The snippet of code where I try to set the number format and insert the sum formulas is below. Thank you in advance!
I know there are a number of threads dealing with this topic, but everything I have looked at seems to have the issue resolved at 'set numberformat', and that is not solving my problem, so I decided to post a new thread.
I am working in access and excel 2003. I am using copyfromrecordset to paste some query results into excel and then after that, I want to insert sum formulas below the pasted range- so I need the pasted values to be in currency or number format.
Here is where the problem is: I get the pasting and formula set up all happen perfectly well, but the sums are all 0, because the pasted range is formatted as text or general by default.
I tried using range(...).numberformat= "Number"
and range(...).numberformat= "$#.##" (what I really need is the currency format) after the data are already pasted. That did nothing-the range is still text. I also tried setting the right number formatting to the sheet before the data are pasted- no luck there either.
Generally, when I run into trouble with changing the format from text to something else in excel, I manually do text to columns on the selected columns and that changes the format to what I have just set. However, doing this manually is not an option for me- just too many workbooks with too many sheets.
Should I just try to do text to columns on the selected range programatically? I am willing to try that, but somehow I am sensing that this is not the most elegant- or even correct - solution to this. I feel like I should just be able to resolve things by simply setting the numberformat. Please post any suggestions. The snippet of code where I try to set the number format and insert the sum formulas is below. Thank you in advance!
PHP:
wb.sheets(currentsubtasktemplate).range(namecol & ":" & TOTALScol).NumberFormat = "$#.##"
'... setting recordset code here
'pasting:
wb.sheets(currentsubtasktemplate).range(namecol & header_PSreg + 1).CopyFromRecordset rs_monthsal
Debug.Print "pasted values"
'inserting sums below the pasted range:
Dim lastrowreg As Long
lastrowreg = monthsal_recordcount + header_PSreg + 1
'insert SUM row into PS REG section:
'Range("S3:S461").NumberFormat = "Number"
wb.sheets(currentsubtasktemplate).range(namecol & headerPS_reg + 1 & ":" & TOTALScol & lastrowreg - 1).NumberFormat = "$#.##"
With wb.sheets(currentsubtasktemplate)
.range(totsalcol & lastrowreg).Value = _
xlApp.worksheetfunction.sum(.range(totsalcol & monthsal_recordcount + header_PSreg & ":" & _
totsalcol & header_PSreg + 1))
.range(chargepertaskcol & lastrowreg).Value = _
xlApp.worksheetfunction.sum(.range(chargepertaskcol & monthsal_recordcount + header_PSreg & ":" & _
chargepertaskcol & header_PSreg + 1))
.range(fringecol & lastrowreg).Value = _
xlApp.worksheetfunction.sum(.range(fringecol & monthsal_recordcount + header_PSreg & ":" & _
fringecol & header_PSreg + 1))
.range(TOTALScol & lastrowreg).Value = _
xlApp.worksheetfunction.sum(.range(TOTALScol & monthsal_recordcount + header_PSreg & ":" & _
TOTALScol & header_PSreg + 1))
.range(namecol & lastrowreg & ":" & TOTALScol & lastrowreg).Font.Bold = True
End With
