Mobile phones are truncated and 0 is missing VBA

vbanewbie68

Board Regular
Joined
Oct 16, 2021
Messages
171
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Dear Sir or Madam

1637149764856.png


For using a macro I download a data report from the website's platform, and one of the donor’s mobile phones has an error, the numbers are truncated. Please see my screenshot. How do I resolve this please?

Also, the ‘0’ digit are missing from the start of the mobile numbers as well. What is the best way of doing this?

For the above, I am using a macro file to download a report.

Hope this makes sense.

Best Regards

vbanewbie68
 
Yes that is right download CSV file before running the maacro.

please see my line for opening to get a downloaded report.

'Imprt Sheet

ChDrive ("J")



ChDir ("J:\PS\FSD_Rest\SOPS_Data")

' Set File Name to selected File

FilePath = Application.GetOpenFilename(, , "Select Report")



Set = ThisWorkbook.Sheets.Add

Imprt

If FilePath <> "False" Then 'if filepath selected...

Call copyWorkbookasText(Imprt.Cells(1, 1), FilePath)

Else

Application.ScreenUpdating = True

MsgBox "No file selected!"

End

End If

Application.ScreenUpdating = True



'Modify Sheet



Call Add_Columns

Call Add_Rows_Data



'Export Sheet to new workbook (let the user save for themselves later)



Imprt.Move





End Sub





Sub copyWorkbookasText(targetRange As Range, FilePath As String)

'Copy over contents of report

Application.ScreenUpdating = False

Application.DisplayAlerts = False

Workbooks.Open FilePath, ReadOnly:=True

If ActiveWorkbook.FileFormat = 51 Then '.xlsx files

ActiveWorkbook.Sheets(1).Cells.Copy targetRange

ActiveWorkbook.Close

Application.ScreenUpdating = True

Application.DisplayAlerts = True

Exit Sub

Else

Colcount = ActiveWorkbook.Sheets(1).Columns.Count - 1

ActiveWorkbook.Close

Application.ScreenUpdating = True

Application.DisplayAlerts = True

End If

'Columns to ensure we imprt file as text

ReDim TextArray(0 To Colcount) As Variant

For i = 0 To Colcount

If i = 0 Then

TextArray(i) = 4

Else

TextArray(i) = 2 '2 is the value assigned to 'Text' '

End If

Next i



'Imprt the text file into excel as text

With ActiveSheet.QueryTables.Add(Connection:= _

"TEXT;" & FilePath, Destination:=targetRange)

.TextFilePlatform = 850

.FieldNames = True

.RowNumbers = False

.FillAdjacentFormulas = False

.PreserveFormatting = True

.RefreshOnFileOpen = False

.RefreshStyle = xlInsertDeleteCells

.SavePassword = False

.SaveData = True

.AdjustColumnWidth = True

.RefreshPeriod = 0

.TextFilePromptOnRefresh = False

.TextFileStartRow = 1

.TextFileParseType = xlDelimited

.TextFileTextQualifier = xlTextQualifierDoubleQuote

.TextFileConsecutiveDelimiter = False

.TextFileTabDelimiter = True

.TextFileSemicolonDelimiter = False

.TextFileCommaDelimiter = True

.TextFileSpaceDelimiter = False

.TextFileColumnDataTypes = TextArray '!!!!Note, use of TextArray from code above!!!

.TextFileTrailingMinusNumbers = True

.Refresh BackgroundQuery:=False

End With

End Sub




End Sub


Sub copyWorkbookasText(targetRange As Range, FilePath As String)
'Copy over contents of report
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Workbooks.Open FilePath, ReadOnly:=True
If ActiveWorkbook.FileFormat = 51 Then '.xlsx files
ActiveWorkbook.Sheets(1).Cells.Copy targetRange
ActiveWorkbook.Close
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Exit Sub
Else
Colcount = ActiveWorkbook.Sheets(1).Columns.Count - 1
ActiveWorkbook.Close
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End If
'Columns to ensure we import file as text
ReDim TextArray(0 To Colcount) As Variant
For i = 0 To Colcount
If i = 0 Then
TextArray(i) = 4
Else
TextArray(i) = 2 '2 is the value assigned to 'Text' '
End If
Next i

'Import the text file into excel as text
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & FilePath, Destination:=targetRange)
.TextFilePlatform = 850
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = TextArray '!!!!Note, use of TextArray from code above!!!
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub
 
Upvote 0

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Where exactly in your code is "Imprt" being defined and set?
I see lots of references to it, but nowhere that shows what it is actually being set to.
 
Upvote 0
This is what I have been advised about that. Basically I go to Go Donate platform to get a csv report and save it onto a folder.

I use macro to open the csv report with added extra columns.

I save the report to be ready to upload onto an import tool for Raisers Edge.

I am new to VBA.

Regards
 
Upvote 0
This is what I have been advised about that. Basically I go to Go Donate platform to get a csv report and save it onto a folder.

I use macro to open the csv report with added extra columns.

I save the report to be ready to upload onto an import tool for Raisers Edge.

I am new to VBA.

Regards
No, I am asking where exactly in your VBA code is "Imprt" being defined?

It should be a line that starts like:
VBA Code:
Set Imprt = ...

Could you post that section of your code?
 
Upvote 0
Public FilePath As String, Imprt As Worksheet
Sub Main()

'Imprt Sheet
ChDrive ("J")

ChDir ("J:\PS\FSD_Rest\SOPS_Data")
' Set File Name to selected File
FilePath = Application.GetOpenFilename(, , "Select Report")

Set Imprt = ThisWorkbook.Sheets.Add

If FilePath <> "False" Then 'if filepath selected...
Call copyWorkbookasText(Import.Cells(1, 1), FilePath)
Else
Application.ScreenUpdating = True
MsgBox "No file selected!"
End
End If
Application.ScreenUpdating = True

'Modify Sheet

Call Add_Columns
Call Add_Rows_Data

'Export Sheet to new workbook (let the user save for themselves later)

Imprt.Move


End Sub
 
Upvote 0
One way is to change the way you open this download file.
The first thing I would do is change the extension from "CSV" to "TXT".
Then, instead of automatically opening the file and performing its automated conversions, it opens the Import Wizard and allows you to control the import of the file, and you can set this Mobile Phone field to Text. If you do that, then since that field will be brought in as Text, leading zeroes will be maintained, and it will not put the values in scientific notation.

These steps can be automated.
This link shows you how to rename the file with VBA: VBA Rename File - Automate Excel
And if you record yourself opening the renamed file and going through the steps of the Import Wizard and setting the proper format of this mobile number field, then you will have recorded VBA code that you should be able to insert into your current code and do what you want.
 
Upvote 0
One way is to change the way you open this download file.
The first thing I would do is change the extension from "CSV" to "TXT".
Then, instead of automatically opening the file and performing its automated conversions, it opens the Import Wizard and allows you to control the import of the file, and you can set this Mobile Phone field to Text. If you do that, then since that field will be brought in as Text, leading zeroes will be maintained, and it will not put the values in scientific notation.

These steps can be automated.
This link shows you how to rename the file with VBA: VBA Rename File - Automate Excel
And if you record yourself opening the renamed file and going through the steps of the Import Wizard and setting the proper format of this mobile number field, then you will have recorded VBA code that you should be able to insert into your current code and do what you want.
how would you change the extension from CSV to TXT? Use Save As and then use this

1637163179142.png
 
Upvote 0
Thanks for that. I am looking at the link so where about can I add the exact code onto my vba? Will it be on the Sub Main ()?
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top