I have an export from an SQL Server which keeps integer columns as text on the export, is there a way to convert the string to integer when I open the workbook, the columns are Y, Z, AA

I have no control over the exports so I can't get to the SQL Server

I have searched but can't find a solution, I do appoligse if this has been raised before (I am sure it has!).

If I could get the code I can then impliment this to resolve an issue for our department

I have tried the record on Text to Column but it will only let me do a single column, even if I record 3 separate macros they don't do the next one

Here is the recorded code plus the call macro

Sub convert3()
Columns("Y:Y").Select
Selection.TextToColumns Destination:=Range("Y1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Range("Y1").Select


End Sub
Sub convert1()
Columns("Z:Z").Select
Selection.TextToColumns Destination:=Range("Z1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Range("z1").Select

End Sub
Sub convert2()
Columns("AA:AA").Select
Selection.TextToColumns Destination:=Range("AA1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Range("AA1").Select

End Sub
Sub convert4()
Call convert1
Call convert2
Call convert3
End Sub

How about something like:

Columns("Y:AA").Select
Selection.Copy
Range("AB1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd, SkipBlanks:=False, Transpose:=False
Columns("Y:AA").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft

How about something like:

Columns("Y:AA").Select
Selection.Copy
Range("AB1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd, SkipBlanks:=False, Transpose:=False
Columns("Y:AA").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft


Just a word of warning about formatting entire columns or rows. Doing so increases the file size dramatically of the Excel workbook. It has to do with how it keeps track of formatting. So, if you use formatting code you should ONLY format the areas in use and not the whole column(s) or row(s). I learned this the hard way while working on a project. I formatted a couple of columns and rows and came up with a 25 Mb file but if I formatted just the area that had data it was under 1 Mb.

Thank you both for your input, both very valid.

I found that 2 of the columns had £ symbols, so I couldn't take advantage of pastespecial by itself. What I ended up with was 2 methods to resolve, one was to use the convert to text feature under the Data Tab (using Excel 2007), but this then needed to include a edit replace because of the symbol, so the code became:


Columns("Y:Y").TextToColumns Destination:=Range("Y1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("Z:Z").Replace What:="£", Replacement:=""
Columns("AA:AA").Replace What:="£", Replacement:=""


The other was to use the Value formula and loop through all the cells, I was formatting the whole columns and therefore the size of the workbook would increase (although it isn't a large book).

The second set of code is:


Sheets(1).Select
Range("a4").Select
Range("AB3") = "Limit (Orig Curr)"
Range("ac3") = "Deductible (£)"
Range("ad3") = "QBE Premium (£)"
Do Until ActiveCell.Value = ""
ActiveCell.Offset(0, 27).FormulaR1C1 = "=VALUE(RC[-3])"
ActiveCell.Offset(0, 28).FormulaR1C1 = "=VALUE(RC[-3])"
ActiveCell.Offset(0, 29).FormulaR1C1 = "=VALUE(RC[-3])"
ActiveCell.Offset(1, 0).Select

Loop
Columns("AB:AD").Select
Selection.Copy
Columns("Y:AA").Select
Selection.PasteSpecial Paste:=xlPasteValues
Columns("AB:AD").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("a1").Select