Hello
I need your help.
I would like to include in the excel file "GEONAMES - EXTRACT AND SETTING" a macro that converts the file text contained in the "GEONAMES - FILE TEXT" folder into excel files that will have the same text file names and will be placed in the "GEONAMES - FILE EXCEL ".
When converting Excel files, i need:
1) Format columns as text
2) Provide conversion with character encoding 65001, utf-8;
3) Foresee the alignment to the left of the data contained in the columns;
4) Insert the following column headers into the file:
GEONAMEID
NAME
ASCIINAME
ALTERNATENAMES
LATITUDE
LONGITUDE
FEATURE CLASS
FEATURE CODE
COUNTRY CODE
CC2
ADMIN1 CODE
ADMIN2 CODE
ADMIN3 CODE
ADMIN4 CODE
POPULATION
ELEVATION
DEM
TIMEZONE
DATE MODIFICATION
5) Make sure the Excel excel text does not change the geographic coordinates columns as they are in the text file (I noticed that transformation can turn coordinates into numbers).
For conversion code and points from number 1 to number 3 you may see the macro contained in the file you see here:
The macro must be activated by a button at the highlighted blue "GEONAMES - FILE EXCEL" text of the fiel excel.
Thank you
Link of the folder containing the excel file and excel text and text files:https://www.dropbox.com/s/aojqbkwv65enr0d/GEONAMES.7z?dl=0
MOD EDIT: CODE TAGS ADDED
I need your help.
I would like to include in the excel file "GEONAMES - EXTRACT AND SETTING" a macro that converts the file text contained in the "GEONAMES - FILE TEXT" folder into excel files that will have the same text file names and will be placed in the "GEONAMES - FILE EXCEL ".
When converting Excel files, i need:
1) Format columns as text
2) Provide conversion with character encoding 65001, utf-8;
3) Foresee the alignment to the left of the data contained in the columns;
4) Insert the following column headers into the file:
GEONAMEID
NAME
ASCIINAME
ALTERNATENAMES
LATITUDE
LONGITUDE
FEATURE CLASS
FEATURE CODE
COUNTRY CODE
CC2
ADMIN1 CODE
ADMIN2 CODE
ADMIN3 CODE
ADMIN4 CODE
POPULATION
ELEVATION
DEM
TIMEZONE
DATE MODIFICATION
5) Make sure the Excel excel text does not change the geographic coordinates columns as they are in the text file (I noticed that transformation can turn coordinates into numbers).
For conversion code and points from number 1 to number 3 you may see the macro contained in the file you see here:
Code:
Option Explicit
Sub ImportTextFileTabSeparatedNewSheets()
' constants
' declarations
Dim sPath As String, sFile As String, sName As String
Dim I As Integer, A As String
' start
sPath = ThisWorkbook.Path
sFile = Dir(sPath & "\*.txt")
With ThisWorkbook
Worksheets(.Worksheets.Count).Activate
End With
' process
Do Until sFile = ""
' worksheet
ActiveWorkbook.Worksheets.Add After:=Worksheets(Worksheets.Count)
On Error Resume Next
sName = Left(sFile, InStr(sFile, ".") - 1)
ActiveSheet.Name = sName
On Error GoTo 0
' text file
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & sPath & "\" & sFile, Destination:=Range("$A$2"))
.Name = sName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 65001
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierNone
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
With ActiveSheet.QueryTables
.Item(.Count).Delete
End With
' titles
I = 1
With ActiveSheet
Do While Worksheets(1).Cells(2, I + 4) <> ""
.Cells(1, I) = Worksheets(1).Cells(2, I + 4)
I = I + 1
Loop
End With
ActiveSheet.Cells.Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
' link
With Worksheets(1)
.Activate
I = .Cells(1, 1).End(xlDown).End(xlDown).End(xlUp).Row + 1
.Cells(I, 1).Value = sName
.Cells(I, 2).Select
.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'" & sName & "'!A1", TextToDisplay:=sName
End With
' cycle
sFile = Dir()
Loop
' end
ThisWorkbook.Worksheets(1).Activate
Range("A1").Select
Beep
End SubThe macro must be activated by a button at the highlighted blue "GEONAMES - FILE EXCEL" text of the fiel excel.
Thank you
Link of the folder containing the excel file and excel text and text files:https://www.dropbox.com/s/aojqbkwv65enr0d/GEONAMES.7z?dl=0
MOD EDIT: CODE TAGS ADDED
Last edited by a moderator:
