Hello all,
I am using this bit of Excel Automation code in Access 2003 VBA to copy data
from one tab to another. Problem is, column A is being presented in Excel as
text. I would like column A to be converted to number via code.

Any assistance you can provide is appreciated.

20 strFile = "C:\Program Files\Database\TRANSFERS.xls"

'This will export the query to the spreadsheet. Since there is
already a Sheet1 tab,
'it will create a tab titled Sheet11
30 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qry_List", strFile, True, "Sheet1"



150 Set objXLBook = GetObject(strFile)


' We can use the Parent
' property of the workbook object
' to get a pointer to Excel's
' Application object
160 Set objXLApp = objXLBook.Parent

170 objXLBook.Windows(1).Visible = True


180 Set obj_Sheet1 = objXLBook. _
Worksheets("Sheet1")

190 Set obj_Sheet11 = objXLBook. _
Worksheets("Sheet11")



200 LastRow = obj_Sheet11.UsedRange.Rows.Count
210 If LastRow > 1 Then
220 obj_Sheet11.Range("A1:J" & LastRow).Copy


230 obj_Sheet1.Range("A1:J1").pastespecial -4163
240 obj_Sheet1.Range("A1").select

Please try this:
With obj_Sheet1
LstRow = .Range("A1").End(xlDown).Row
.Cells(LstRow + 1, 1).Value = "1"
.Cells(LstRow + 1, 1).Copy
With Range("A1:A" & LstRow)
.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
.NumberFormat = "0.00"
End With
.Cells(LstRow + 1, 1).ClearContents
End With


"David" wrote:

> Hello all,
> I am using this bit of Excel Automation code in Access 2003 VBA to copy data
> from one tab to another. Problem is, column A is being presented in Excel as
> text. I would like column A to be converted to number via code.
>
> Any assistance you can provide is appreciated.
>
> 20 strFile = "C:\Program Files\Database\TRANSFERS.xls"
>
> 'This will export the query to the spreadsheet. Since there is
> already a Sheet1 tab,
> 'it will create a tab titled Sheet11
> 30 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
> "qry_List", strFile, True, "Sheet1"
>
>
>
> 150 Set objXLBook = GetObject(strFile)
>
>
> ' We can use the Parent
> ' property of the workbook object
> ' to get a pointer to Excel's
> ' Application object
> 160 Set objXLApp = objXLBook.Parent
>
> 170 objXLBook.Windows(1).Visible = True
>
>
> 180 Set obj_Sheet1 = objXLBook. _
> Worksheets("Sheet1")
>
> 190 Set obj_Sheet11 = objXLBook. _
> Worksheets("Sheet11")
>
>
>
> 200 LastRow = obj_Sheet11.UsedRange.Rows.Count
> 210 If LastRow > 1 Then
> 220 obj_Sheet11.Range("A1:J" & LastRow).Copy
>
>
> 230 obj_Sheet1.Range("A1:J1").pastespecial -4163
> 240 obj_Sheet1.Range("A1").select
>
>

Thanks a lot! I agree with Srikanth. This is the best solution. After minor tweaking of your code I was able to implement so that I do not have to loop thru all the cells that I was doing earlier.

Thanks a lot for this solution.



Glea wrote:

RE: Convert to Number
07-Dec-07

Please try this
With obj_Sheet
LstRow = .Range("A1").End(xlDown).Ro
.Cells(LstRow + 1, 1).Value = "1
.Cells(LstRow + 1, 1).Cop
With Range("A1:A" & LstRow
.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply,
SkipBlanks:=False, Transpose:=Fals
Application.CutCopyMode = Fals
.NumberFormat = "0.00
End Wit
.Cells(LstRow + 1, 1).ClearContent
End Wit

"David" wrote:

Previous Posts In This Thread:

On Friday, December 07, 2007 3:54 PM
Davi wrote:

Convert to Number
Hello all
I am using this bit of Excel Automation code in Access 2003 VBA to copy data
from one tab to another. Problem is, column A is being presented in Excel as
text. I would like column A to be converted to number via code

Any assistance you can provide is appreciated

20 strFile = "C:\Program Files\Database\TRANSFERS.xls

'This will export the query to the spreadsheet. Since there is
already a Sheet1 tab
'it will create a tab titled Sheet1
30 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qry_List", strFile, True, "Sheet1


150 Set objXLBook = GetObject(strFile

' We can use the Paren
' property of the workbook objec
' to get a pointer to Excel'
' Application objec
160 Set objXLApp = objXLBook.Paren

170 objXLBook.Windows(1).Visible = Tru

180 Set obj_Sheet1 = objXLBook.
Worksheets("Sheet1"

190 Set obj_Sheet11 = objXLBook.
Worksheets("Sheet11"


200 LastRow = obj_Sheet11.UsedRange.Rows.Coun
210 If LastRow > 1 The
220 obj_Sheet11.Range("A1:J" & LastRow).Cop


230 obj_Sheet1.Range("A1:J1").pastespecial -416
240 obj_Sheet1.Range("A1").select

On Friday, December 07, 2007 5:01 PM
Glea wrote:

RE: Convert to Number
Please try this
With obj_Sheet
LstRow = .Range("A1").End(xlDown).Ro
.Cells(LstRow + 1, 1).Value = "1
.Cells(LstRow + 1, 1).Cop
With Range("A1:A" & LstRow
.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply,
SkipBlanks:=False, Transpose:=Fals
Application.CutCopyMode = Fals
.NumberFormat = "0.00
End Wit
.Cells(LstRow + 1, 1).ClearContent
End Wit

"David" wrote:

On Monday, July 13, 2009 9:55 AM
Rallabhandi Venkata Srikanth wrote:

Convert to number
Hi

Out of zillion google searches, your tip is the BEST!

Thanks a lot, it helped me

Regards
Srikanth


Submitted via EggHeadCafe - Software Developer Portal of Choice
ActiveSync 4.1 Released
http://www.eggheadcafe.com/tutorials...-released.aspx

See if this will work:

Sub TextToNumbers()
' Converting text numbers to real numbers
' Using the the used range for selection of cells
'
Dim cellval As Range
Dim myRng As Range
Set myRng = ActiveSheet.UsedRange
For Each cellval In myRng
cellval = cellval.Value
Next
End Sub

HTH
--
Data Hog


"Anuj Anand" wrote:

> Thanks a lot! I agree with Srikanth. This is the best solution. After minor tweaking of your code I was able to implement so that I do not have to loop thru all the cells that I was doing earlier.
>
> Thanks a lot for this solution.
>
>
>
> Glea wrote:
>
> RE: Convert to Number
> 07-Dec-07
>
> Please try this:
> With obj_Sheet1
> LstRow = .Range("A1").End(xlDown).Row
> .Cells(LstRow + 1, 1).Value = "1"
> .Cells(LstRow + 1, 1).Copy
> With Range("A1:A" & LstRow)
> .PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
> SkipBlanks:=False, Transpose:=False
> Application.CutCopyMode = False
> .NumberFormat = "0.00"
> End With
> .Cells(LstRow + 1, 1).ClearContents
> End With
>
>
> "David" wrote:
>
> Previous Posts In This Thread:
>
> On Friday, December 07, 2007 3:54 PM
> Davi wrote:
>
> Convert to Number
> Hello all,
> I am using this bit of Excel Automation code in Access 2003 VBA to copy data
> from one tab to another. Problem is, column A is being presented in Excel as
> text. I would like column A to be converted to number via code.
>
> Any assistance you can provide is appreciated.
>
> 20 strFile = "C:\Program Files\Database\TRANSFERS.xls"
>
> 'This will export the query to the spreadsheet. Since there is
> already a Sheet1 tab,
> 'it will create a tab titled Sheet11
> 30 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
> "qry_List", strFile, True, "Sheet1"
>
>
>
> 150 Set objXLBook = GetObject(strFile)
>
>
> ' We can use the Parent
> ' property of the workbook object
> ' to get a pointer to Excel's
> ' Application object
> 160 Set objXLApp = objXLBook.Parent
>
> 170 objXLBook.Windows(1).Visible = True
>
>
> 180 Set obj_Sheet1 = objXLBook. _
> Worksheets("Sheet1")
>
> 190 Set obj_Sheet11 = objXLBook. _
> Worksheets("Sheet11")
>
>
>
> 200 LastRow = obj_Sheet11.UsedRange.Rows.Count
> 210 If LastRow > 1 Then
> 220 obj_Sheet11.Range("A1:J" & LastRow).Copy
>
>
> 230 obj_Sheet1.Range("A1:J1").pastespecial -4163
> 240 obj_Sheet1.Range("A1").select
>
> On Friday, December 07, 2007 5:01 PM
> Glea wrote:
>
> RE: Convert to Number
> Please try this:
> With obj_Sheet1
> LstRow = .Range("A1").End(xlDown).Row
> .Cells(LstRow + 1, 1).Value = "1"
> .Cells(LstRow + 1, 1).Copy
> With Range("A1:A" & LstRow)
> .PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
> SkipBlanks:=False, Transpose:=False
> Application.CutCopyMode = False
> .NumberFormat = "0.00"
> End With
> .Cells(LstRow + 1, 1).ClearContents
> End With
>
>
> "David" wrote:
>
> On Monday, July 13, 2009 9:55 AM
> Rallabhandi Venkata Srikanth wrote:
>
> Convert to number
> Hi,
>
> Out of zillion google searches, your tip is the BEST!!
>
> Thanks a lot, it helped me.
>
> Regards,
> Srikanth
>
>
> Submitted via EggHeadCafe - Software Developer Portal of Choice
> ActiveSync 4.1 Released
> http://www.eggheadcafe.com/tutorials...-released.aspx
> .
>