Removing beginning minus sign from telephone numbers
 

I have a Excel document which retrieves contact data from a server, processes
it, and creates and sends the document to a predetermined e-mail address. My
problem is that the phone numbers are in "international" format, for example
+46 0123456789... This makes Excel think that it's some kind of mathematic
formula, causing it to put a minus sign (-) before the number, making it
useless. It's possible to use find/replace to correct this, but this is not
an option for me since the whole process has to be automated. I've tried
lot's of different macros etc, but so far I haven't been able to solve this
problem. Is there some way I can tell Excel that the telephone and fax number
field should added to the document with no modifications?

Range("A1") = "Name"
Range("B1") = "Page"
Range("C1") = "Time"
Range("D1") = "DUNS number"
Range("E1") = "Company ID"
Range("F1") = "Address1"
Range("G1") = "Address2"
Range("H1") = "ZIP/postal code"
Range("I1") = "City"
Range("J1") = "Region"
Range("K1") = "Country"
Range("L1") = "Phone"
Range("M1") = "Fax"
Range("N1") = "Website"
Range("O1") = "Number of employees"
Range("P1") = "SIC code"
Range("Q1") = "SIC code text"
Range("R1") = "Sales Euro"
Range("S1") = "Year started"
Rows("1:1").Font.Bold = True
Cells.Columns.AutoFit

I'm guessing that I should modify something here, but I don't know what. All
help is appreciated!

Removing beginning minus sign from telephone numbers
 

It depends on how the data is retrieved.


If the data is retrieved using an Import Wizard, tell the Wizard that the
field is Text. That way Excel will not attempt to re-interpret the values.

Other retrieval methods also allow the user to specify the nature of the
fields being retrieved.
--
Gary's Student


"Henrik Johnson" wrote:

I have a Excel document which retrieves contact data from a server, processes
it, and creates and sends the document to a predetermined e-mail address. My
problem is that the phone numbers are in "international" format, for example
+46 0123456789... This makes Excel think that it's some kind of mathematic
formula, causing it to put a minus sign (-) before the number, making it
useless. It's possible to use find/replace to correct this, but this is not
an option for me since the whole process has to be automated. I've tried
lot's of different macros etc, but so far I haven't been able to solve this
problem. Is there some way I can tell Excel that the telephone and fax number
field should added to the document with no modifications?

Range("A1") = "Name"
Range("B1") = "Page"
Range("C1") = "Time"
Range("D1") = "DUNS number"
Range("E1") = "Company ID"
Range("F1") = "Address1"
Range("G1") = "Address2"
Range("H1") = "ZIP/postal code"
Range("I1") = "City"
Range("J1") = "Region"
Range("K1") = "Country"
Range("L1") = "Phone"
Range("M1") = "Fax"
Range("N1") = "Website"
Range("O1") = "Number of employees"
Range("P1") = "SIC code"
Range("Q1") = "SIC code text"
Range("R1") = "Sales Euro"
Range("S1") = "Year started"
Rows("1:1").Font.Bold = True
Cells.Columns.AutoFit

I'm guessing that I should modify something here, but I don't know what. All
help is appreciated!

Removing beginning minus sign from telephone numbers
 

The data is not imported using the Import Wizard. I would like to do exactly
what you describe, only by not using the Wizard...

Removing beginning minus sign from telephone numbers
 

Pre-format the column(s) as text?

--
Don Guillett
SalesAid Software

"Henrik Johnson" <Henrik wrote in message
...
I have a Excel document which retrieves contact data from a server,
processes
it, and creates and sends the document to a predetermined e-mail address.
My
problem is that the phone numbers are in "international" format, for
example
+46 0123456789... This makes Excel think that it's some kind of mathematic
formula, causing it to put a minus sign (-) before the number, making it
useless. It's possible to use find/replace to correct this, but this is
not
an option for me since the whole process has to be automated. I've tried
lot's of different macros etc, but so far I haven't been able to solve
this
problem. Is there some way I can tell Excel that the telephone and fax
number
field should added to the document with no modifications?

Range("A1") = "Name"
Range("B1") = "Page"
Range("C1") = "Time"
Range("D1") = "DUNS number"
Range("E1") = "Company ID"
Range("F1") = "Address1"
Range("G1") = "Address2"
Range("H1") = "ZIP/postal code"
Range("I1") = "City"
Range("J1") = "Region"
Range("K1") = "Country"
Range("L1") = "Phone"
Range("M1") = "Fax"
Range("N1") = "Website"
Range("O1") = "Number of employees"
Range("P1") = "SIC code"
Range("Q1") = "SIC code text"
Range("R1") = "Sales Euro"
Range("S1") = "Year started"
Rows("1:1").Font.Bold = True
Cells.Columns.AutoFit

I'm guessing that I should modify something here, but I don't know what.
All
help is appreciated!

Removing beginning minus sign from telephone numbers
 

Exactly. What's the VBS command to do this, and where should it be entered?

"Don Guillett" wrote:

Pre-format the column(s) as text?

--
Don Guillett
SalesAid Software

Removing beginning minus sign from telephone numbers
 

try recording a macro while you do it to see. You will get
Columns("C:C").Select
Selection.NumberFormat = "@"

modify to
Columns("C").NumberFormat = "@"
and change the column to suit

Don Guillett
SalesAid Software

"Henrik Johnson" wrote in message
...
Exactly. What's the VBS command to do this, and where should it be
entered?

"Don Guillett" wrote:

Pre-format the column(s) as text?

--
Don Guillett
SalesAid Software

Removing beginning minus sign from telephone numbers
 

I can't seem to figure out where the code should be added. I'll paste the
entire script here, maybe it will make some sense... I've edited out the
actual server URL...

Sub Mail_ActiveSheet()

End Sub

Sub Run()
Dim count As Integer

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False

count = 2

While Not count = ActiveSheet.UsedRange.Rows.count
If Not Range("A" & count) = "" Then
SendMail (count)
End If
count = count + 1
Wend

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub


'Sub Run()
'Dim count As Integer
'count = 2
'While Not Range("A" & count) = ""
'count = count + 1
'Wend

'count = count - 1

'For i = 2 To count
' SendMail (i)
'Next

'End Sub

Function SendMail(ByVal RowIndex As Integer)

Dim StartDate As String
Dim EndDate As String
Dim Username As String
Dim Password As String
Dim Email As String
Dim Firma As String

Username = Range("b" & RowIndex)
Password = Range("c" & RowIndex)
Email = Range("d" & RowIndex)
Firma = Range("a" & RowIndex)



EndDate = Format(Now, "yyyy-mm-dd")
StartDate = Format(Now - 7, "yyyy-mm-dd")


Dim UserFile As String
UserFile = "http://url.to.server.com/webtrack/report/execute?id=6&from=" &
StartDate & "&to=" & EndDate & "&type=csv&execute=&j_username=" & Username &
"&j_password=" & Password


Workbooks.OpenText Filename:= _
UserFile, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2,
1), Array( _
3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8,
1), Array(9, 1), Array(10 _
, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1),
Array(15, 1), Array(16, 1), _
Array(17, 1), Array(18, 1), Array(19, 1)), TrailingMinusNumbers:=True

Rows(1).Delete Shift:=xlToLeft

Columns.EntireColumn.Sort Key1:=Range("C1"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Dim i As Integer
Dim count As Integer
count = 1


Columns("C:C").Cut
Columns("A:A").Insert Shift:=xlToRight

Columns("T:T").Cut
Columns("C:C").Insert Shift:=xlToRight

While Not Range("A" & count) = ""
count = count + 1
Wend

count = count - 1

For i = 1 To count
If Range("A" & i).Value = Range("A" & i + 1).Value And Not Range("A" & i)
= "" And Not Range("A" & i + 1) = "" And Not Range("A" & i + 2) = "" And Not
Range("A" & i + 3) = "" And Not Range("A" & i + 4) = "" And Not Range("A" & i
+ 5) = "" Then
DeleteRows (i)
End If
Next

Columns("D:D").Delete Shift:=xlToLeft
'Columns("J:J").Delete Shift:=xlToLeft
Rows(1).Insert
Range("A1") = "Name"
Range("B1") = "Page"
Range("C1") = "Time"
Range("D1") = "DUNS number"
Range("E1") = "Company ID"
Range("F1") = "Address1"
Range("G1") = "Address2"
Range("H1") = "ZIP/postal code"
Range("I1") = "City"
Range("J1") = "Region"
Range("K1") = "Country"
Range("L1") = "Phone"
Range("M1") = "Fax"
Range("N1") = "Website"
Range("O1") = "Number of employees"
Range("P1") = "SIC code"
Range("Q1") = "SIC code text"
Range("R1") = "Sales Euro"
Range("S1") = "Year started"
Rows("1:1").Font.Bold = True
Cells.Columns.AutoFit




Dim wb As New Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy")
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs Firma _
& " " & strdate & ".xls"
.SendMail Email, _
"Ukens rapport fra Enecto"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
Windows("execute").Close (False)


Logout


End Function

Sub Logout()
ActiveWorkbook.FollowHyperlink
Address:="http://url.to.server/webtrack/logout", NewWindow:=False
End Sub

Sub DeleteRows(ByVal i As Integer)
Dim y As Integer
y = i
While Range("A" & i).Value = Range("A" & y + 1).Value
Range("A" & y + 1).ClearContents
Range("D" & y + 1, "R" & y + 1).ClearContents
y = y + 1
Wend
End Sub