Notes tests, string, manipulation of text files and string manipulations
One important note at the outset of these notes is that my Excel is default German. I expect therefore issues likely to arise in anything to do with use of a comma , because often the comma , in English for certain things in Excel is replaced with the semi colon ; in German Excel.
Wot’s in a .csv file
In many Excel versions, you have the choice of three different Save As options related to .csv extension files https://excel.tips.net/T002519_Comma...C_and_Mac.html
So lets have a quick look at what the differences are…
In each of the 3 cases I will
make a virgin default template, save it
make a virgin default template, put a value in first cell, save it
make a virgin default template, put a value in first 2 cells, save it
make a virgin default template, put a value in first 2 cells, and in the cell A2, save it
( Initially, when I put values in, I wont hit the Enter after: Initially I want to avoid purposely doing something that may introduce a carriage return or line feed. I will then look further at that issue later )
After this I will investigate the made files , ( using , for example, my Sub WtchaGot_Unic_NotMuchIfYaChoppedItOff(ByVal strIn As String) ) ' https://excelfox.com/forum/showthrea...ts-of-a-string ' https://excelfox.com/forum/showthrea...ll=1#post11015
https://excelfox.com/forum/showthrea...ll=1#post13699
Here are the results ( It is a summary of what my function tells me is in those made files ) :
| CSV (Comma delimited)Empty.csv |
|
vbCr & vbLf |
| CSV (Macintosh)Empty.csv |
|
vbCr & vbLf |
| CSV (MS-DOS)Empty.csv |
|
vbCr & vbLf |
| CSV (Comma delimited)A1.csv |
cellA1 |
"cellA1" & vbCr & vbLf |
| CSV (Macintosh)A1.csv |
cellA1 |
"cellA1" & vbCr & vbLf |
| CSV (MS-DOS)A1.csv |
cellA1 |
"cellA1" & vbCr & vbLf |
| CSV (Comma delimited)A1B1.csv |
cellA1;cellB1 |
"cellA1" & ";" & "cellB1" & vbCr & vbLf |
| CSV (Macintosh)A1B1.csv |
cellA1;cellB1 |
"cellA1" & ";" & "cellB1" & vbCr & vbLf |
| CSV (MS-DOS)A1B1.csv |
cellA1;cellB1 |
"cellA1" & ";" & "cellB1" & vbCr & vbLf |
| CSV (Comma delimited)A1B1A2.csv |
cellA1;cellB1
callA2; |
"cellA1" & ";" & "cellB1" & vbCr & vbLf & "callA2" & ";" & vbCr & vbLf |
| CSV (Macintosh)A1B1A2.csv |
cellA1;cellB1
callA2; |
"cellA1" & ";" & "cellB1" & vbCr & "callA2" & ";" & vbCr & vbLf |
| CSV (MS-DOS)A1B1A2.csv |
cellA1;cellB1
callA2; |
"cellA1" & ";" & "cellB1" & vbCr & vbLf & "callA2" & ";" & vbCr & vbLf |
Important Conclusions are
_ the Macintosh distinguishes itself with a carriage return character, vbCr , as the line separator for introduced lines
_ There is always a last vbCr & vbLf – Note this means that for a single line, or empty file, we could not tell if we had a Macintosh
When closing the file, I was prompted to answer if I wanted to save changes or not. ( I chose yes in the last experiment ). This is strange since I had previously saved the files before closing
DoYouWantToSaveChangesOnCloseDespiteAlreadySavedCS V.JPG : https://imgur.com/nfnVwSF
But it does not seem to have any effect if I chose Yes or No
Some other observations.
If I use a simple macro, as below, to save and close the file ( and except the changes, which I am still strangely asked for , with Yes), then I get commas instead for the separator/delimiter
Code:
Sub SaveCSVviaVBA()
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub
I get the same results for answering No
These macros gives me the same results
Code:
Sub SaveAsCSVviaVBA()
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & "csv Text file Chaos\" & "CSV (Comma delimited)A1B1A2" & ".csv"
ActiveWorkbook.Close
End Sub
Code:
Sub SaveAsCSVviaVBAxlcsv()
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & "csv Text file Chaos\" & "CSV (Comma delimited)A1B1A2" & ".csv", FileFormat:=xlCSV
ActiveWorkbook.Close
End Sub
Ref
https://excelfox.com/forum/showthrea...mediate-Window
https://excelfox.com/forum/showthrea...ed-Text-String
https://excel.tips.net/T002519_Comma...C_and_Mac.html
https://excelribbon.tips.net/T010280...for_PC_and_Mac
https://sites.google.com/a/madrocket...svs?authuser=0
https://excelribbon.tips.net/T009508...ariations.html
Eileen’sLoungeTextFiles
http://www.eileenslounge.com/viewtop...274367#p274367
https://excel.tips.net/T003232_Speci...n_a_Macro.html -- printer line save ……. This works this way by design in VBA. The Excel implementation of the export routines for VBA always use whatever the Windows regional settings are to determine how items in a CSV should be separated. Specifically, the routine looks at the List Separator field for the delimiter. This means that you can, if desired, change the delimiter to a semicolon by changing the List Separator setting in your regional settings configuration.
If you don't want to change the regional settings, then you can instead write your own macro that will output the file in any way you desire. Consider, for a moment, the following macro, which will output the file:
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iK75iCEaGN
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iK7XF33njy
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iKCSgpAqA1
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iKCy--3x8E
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwNaJiNATXshvJ0Zz94AaABAg. 9iEktVkTAHk9iF9_pdshr6
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iFAZq-JEZ-
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgxV2r7KQnuAyZVLHH54AaABAg. 9iDVgy6wzct9iFBxma9zXI
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwnYuSngiuYaUhEMWN4AaABAg. 9iDQN7TORHv9iFGQQ5z_3f
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwJ3yzdk_EE98dndmt4AaABAg. 9iDLC2uEPRW9iFGvgk11nH
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgyDWAVqCa4yMot463x4AaABAg. 9iH3wvUZj3n9iHnpOxOeXa
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwvLFdMEAba5rLHIz94AaABAg. 9iGReNGzP4v9iHoeaCpTG8
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iHpsWCdJ5I
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=tzbKqTRuRzU&lc=UgyYW2WZ2DvSrzUKnJ14AaABAg
https://www.youtube.com/watch?v=UywjKEMjSp0&lc=UgxIySxHPqM1RxtVqoR4AaABAg. 9edGvmwOLq99eekDyfS0CD
https://www.youtube.com/watch?v=UywjKEMjSp0&lc=UgxIySxHPqM1RxtVqoR4AaABAg. 9edGvmwOLq99eevG7txd2c
https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg
https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgzytUUVRyw9U55-6M54AaABAg
https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgzCoa6tOVIBxRDDDbN4AaABAg
https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgyriWOelbVnw4FHWT54AaABAg. 9dPo-OdLmZ09dc21kigjmr
https://www.youtube.com/watch?v=363wd2EtQZ0&lc=UgzDQfo5rJqyVwvv2r54AaABAg
https://www.youtube.com/watch?v=363wd2EtQZ0&lc=UgzHTSka7YppBdmUooV4AaABAg. 9cXui6zzkz09cZttH_-2Gf
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
Strange saving of "csv” files
Strange saving of “csv” files in Excel
Lets clarify and look in more detail at the strange saving of the files. In addition when we do it manually, we will run a macro recording
We will concentrate on the CSV (______)A1B1A2.csv
Comma delimited
Initially, if you are starting from Excel with more than one worksheet showing, then an initial pop up warns you of this:
PopUpWarningOfMultipleSheets.JPG : https://imgur.com/QLscU1a
https://i.imgur.com/QLscU1a.jpg
This initial pop up will not be shown if you have just one sheet showing at the Save stage
Assuming we have said OK at the above pop up, or have not had this pop up, we examine now the set of pop ups that occur always..
In both cases answering with Yes
On the SaveAs ( or Save ) I get this prompt
SaveAs or Save CSV (Comma delimited).JPG : : https://imgur.com/SQPHjWI
WarningOnManualSaveOrSaveAScsv(Comma delimited).JPG : https://imgur.com/QiH8phI
https://i.imgur.com/QiH8phI.jpg
and these 2 prompts on the Close, ( the second of which is identical to that from the Save or SaveAs )
this first being strange since I already just Saved the file
AskToSaveTheChangesAfterSave CSV (Comma delimited).JPG : : https://imgur.com/8ih47Ty
FirstWarningOnManualClose CSV (Comma delimited).jpg : https://imgur.com/dAxojzW
https://i.imgur.com/dAxojzW.jpg
The second is identical to that on the save : https://imgur.com/SQPHjWI
SecondWarningOnManualClose CSV (Comma delimited).JPG : https://imgur.com/TY9eBqq
https://i.imgur.com/TY9eBqq.jpg
The files as seen in test editor, or from my function, after is like
CellA1;CellA2
CellA3;
The given macro coding from the macro recorder.
Code:
Sub CSVTests()
ActiveWorkbook.SaveAs Filename:="F:\Excel0202015Jan2016\ExcelFox\vixer\csv Text file Chaos\CSV (Comma delimited)A1B1A2.csv", FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Save: ActiveWindow.Close
End Sub
If I answer No for the attempt at Save or SaveAs, then I will get the option to save as an Excel file
Answer No on SaveAs or Save CSV (Comma delimited).JPG : https://imgur.com/uXWo8vz
If I answer Yes for the attempt at Save or SaveAs, but then on Close , answer No to the first prompt, then I get that first prompt only: I don’t get the second prompt. But,
_there is a no difference in the macro from the macro recorder
and
_there is no difference in the file seen in a text editor after.
Should I now open manually the file, I find that the file has the values put, one in each cell, to reproduce the file such that it looks exactly as I created it originally
If I run the macro obtained from the macro recorder, then on the SaveAs or Save , I don’t get any prompt
On Close I get the first prompt only
DoYouWantToSaveChangesOnCloseDespiteAlreadySavedCS V with maco.JPG : https://imgur.com/BihBGOH
If I answer Yes or No , the file is saved without the second prompt as was the manual case.
Strangely, my file as seen in a text editor is different to that obtained whilst manually doing the recording: It has now commas as the separator/delimiter
cellA1,cellB1
cellA2,
Should I open this now manually, I will find that my row data is spit as before, but , I loose the columns, - that is to say the entire row information, including the , separator appear in the first column. So what is happening is that the , separator is not being recognised as a separator, and instead is being taken as pure text: My file is being seen as a text file having just rows of text, with no separator: In other words it could be regarded as a text file intended to hold the text values to be inserted into column A of an Excel File-
| Row\Col |
A |
B |
|---|
1 |
CellA1,CellA2 |
|
2 |
CellA3, |
|
The conclusions are that manually closing I always get the ; as separator/delimiter, and by closing with a macro I always get the ,
Lets just say that again in another way. I do anything manually, and I end up with ; as the separator
I do anything with a macro and I end up with the , as separator.
Even if I record a macro when doing it manually, that same macro if run later will end up giving me the , as separator. This last point is likely to be a source of possible confusion
Just for completeness, the next post will check the same for saving as CSV Macintosh and CSV MS-DOS
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
2 Attachment(s)
Convert Excel data range to XML type text file
In support of this post
https://excelfox.com/forum/showthrea...5355#post15355
_____ Workbook: Sample excel file.xls ( Using Excel 2007 32 bit )
| Row\Col |
A |
B |
C |
D |
E |
F |
G |
H |
I |
|---|
| 1 |
Entity ID |
day |
month |
year |
time |
|
1 |
<-- row number |
|
| 2 |
700 |
19 |
2 |
2021 |
08:00 |
|
2 |
|
|
| 3 |
700 |
19 |
2 |
2021 |
08:30 |
|
3 |
|
|
| 4 |
700 |
20 |
2 |
2021 |
09:00 |
|
4 |
|
|
| 5 |
701 |
19 |
2 |
2021 |
09:30 |
|
5 |
|
|
| 6 |
|
|
|
|
|
|
6 |
|
|
| 7 |
|
2 |
3 |
4 |
5 |
<-- column number |
|
|
|
| 8 |
|
|
|
|
|
|
|
|
|
| 9 |
|
Lr=5 |
|
|
|
|
|
|
|
| 10 |
|
|
arrIn()=Range("A1:E5").Value |
1 |
2 |
3 |
4 |
5 |
6 |
| 11 |
|
|
1 |
Entity ID |
day |
month |
year |
time |
|
| 12 |
|
|
2 |
700 |
19 |
2 |
2021 |
08:00 |
|
| 13 |
|
|
3 |
700 |
19 |
2 |
2021 |
08:30 |
|
| 14 |
|
|
4 |
700 |
20 |
2 |
2021 |
09:00 |
|
| 15 |
|
|
5 |
701 |
19 |
2 |
2021 |
09:30 |
|
| 16 |
|
|
6 |
|
|
|
|
|
|
| 17 |
|
|
|
|
Example: arrIn(5, 1) = 701 |
|
|
|
|
text file output
HTML Code:
<forecast>
<Entity>700</Entity>
<data>
<date>
<day>19/<day>
<month>2</month>
<year>2021</year>
</date>
<time>08:00</time>
<time>08:30</time>
</data>
<data>
<date>
<day>20/<day>
<month>2</month>
<year>2021</year>
</date>
<time>09:00</time>
</data>
</forcast>
<forecast>
<Entity>701</Entity>
<data>
<date>
<day>19/<day>
<month>2</month>
<year>2021</year>
</date>
<time>09:30</time>
</data>
</forcast>
Code:
Option Explicit
'
Sub ExcelToXML()
Rem 1 worksheets data info
Dim Ws1 As Worksheet: Set Ws1 = ThisWorkbook.Worksheets.Item(1)
Dim Lr As Long: Let Lr = Ws1.Range("A" & Rows.Count & "").End(xlUp).Row
Dim arrRng() As Variant: Let arrRng() = Ws1.Range("A1:E" & Lr + 1 & "").Value ' +1 is a bodge to help me not get errors when checking 1 row above my data
Rem 2 Do it
Dim TotalFile As String
Dim Rw As Long: Let Rw = 2 ' Main row count
' #STEP 1 Start
Do While Rw <= Lr ' This keeps us going as long as data is there
Let TotalFile = TotalFile & "<forecast>" & vbCr & vbLf & "<Entity>" & arrRng(Rw, 1) & "</Entity>" & vbCr & vbLf: Debug.Print TotalFile
' # STEP 2 start
Let TotalFile = TotalFile & "<data>" & vbCr & vbLf & "<date>" & vbCr & vbLf & "<day>" & arrRng(Rw, 2) & "/<day>" & vbCr & vbLf & "<month>" & arrRng(Rw, 3) & "</month>" & vbCr & vbLf & "<year>" & arrRng(Rw, 4) & "</year>" & vbCr & vbLf & "</date>" & vbCr & vbLf & "<time>" & Format(arrRng(Rw, 5), "hh" & ":" & "mm") & "</time>" & vbCr & vbLf: Debug.Print TotalFile
' #STEP 3 START
' Check if Entity ID in first row = Entity ID in 2nd row and date in first row = date in 2nd row then repeat STEP 3 for 2nd row and so on
Do While Rw + 1 <= Lr And arrRng(Rw, 1) = arrRng(Rw + 1, 1) And arrRng(Rw, 2) = arrRng(Rw + 1, 2)
Let TotalFile = TotalFile & "<time>" & Format(arrRng(Rw + 1, 5), "hh" & ":" & "mm") & "</time>" & vbCr & vbLf: Debug.Print TotalFile
Let Rw = Rw + 1 ' This brings us to the line we just filled in
Loop
Let TotalFile = TotalFile & "</data>" & vbCr & vbLf: Debug.Print TotalFile
' Chect if Entity ID in first row = Entity ID in 2nd row and date in first row not equals to date in 2nd row then repeat STEP 2 for 2nd row and so on
Do While Rw + 1 <= Lr And arrRng(Rw, 1) = arrRng(Rw + 1, 1) ' And Not arrRng(Rw, 2) = arrRng(Rw + 1, 2)
Let TotalFile = TotalFile & "<data>" & vbCr & vbLf & "<date>" & vbCr & vbLf & "<day>" & arrRng(Rw + 1, 2) & "/<day>" & vbCr & vbLf & "<month>" & arrRng(Rw + 1, 3) & "</month>" & vbCr & vbLf & "<year>" & arrRng(Rw + 1, 4) & "</year>" & vbCr & vbLf & "</date>" & vbCr & vbLf & "<time>" & Format(arrRng(Rw + 1, 5), "hh" & ":" & "mm") & "</time>" & vbCr & vbLf: Debug.Print TotalFile
Let Rw = Rw + 1 ' This brings us to the line we just filled in
Loop
Let TotalFile = TotalFile & "</data>" & vbCr & vbLf: Debug.Print TotalFile
' #STEP 3 END
' STEP 2 END
Let TotalFile = TotalFile & "</forcast>" & vbCr & vbLf: Debug.Print TotalFile
Let Rw = Rw + 1 ' ' This brings us to the next line
' STEP 1 END
Loop ' While Rw <= Lr
Let TotalFile = Replace(TotalFile, "</data>" & vbCr & vbLf & "</data>" & vbCr & vbLf, "</data>" & vbCr & vbLf, 1, -1, vbBinaryCompare): Debug.Print TotalFile ' I end up with a double "</data>" & vbCr & vbLf
Rem 3 Make text file
Dim FileNum2 As Long: Let FileNum2 = FreeFile(0) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
Dim PathAndFileName2 As String
Let PathAndFileName2 = ThisWorkbook.Path & "\" & "XML_Stuff.txt" ' ' CHANGE TO SUIT ' Will be made if not there
Open PathAndFileName2 For Output As #FileNum2
Print #FileNum2, TotalFile ' write out entire text file
Close #FileNum2
End Sub
' <forecast> ' #STEP 1 Start Print #intFile, "<Forecast>"
' <Entity>700</Entity> ' #STEP 1 Start Print #intFile, "<Entity>" & Entity ID & "</Entity>"
' <data> ' #STEP 2 Start Print #intFile, "<Data>"
' <date> ' #STEP 2 Start Print #intFile, "<date>"
' <day>19</day> ' #STEP 2 Start Print #intFile, "<day>" & day &
' <month>2</month> ' #STEP 2 Start "</day><month>" & month & "</month>
' <year>2021</year> ' #STEP 2 Start <year>" & yeear & "</year>"
' </date> ' #STEP 2 Start </date>"
' <time>8:00</time> ' #STEP 3 START Print #intFile, "<time>" & time & "</time>"
' Check if Entity ID in first row = Entity ID in 2nd row
' and date in first row = date in 2nd row then
' <time>8:30</time> ' repeat STEP 3 for 2nd row and so on
' </data> ' #STEP 3 END
' Check if Entity ID in first row = Entity ID in 2nd row
' and date in first row IS NOT = date in 2nd row then'
' repeat STEP ??3?? 2 for 2nd row and so on
' <data>
' <date>
' <day>20</day>
' <month>2</month>
' <year>2021</year>
' </date>
' <time> ??8:00?? 9.00 </time>
' </data>
' </forecast> ' STEP 2 END Print #intFile, "</forecast>"
' If Entity ID is not same as in previous row repeat STEP 1
'
' <forecast>
' <Entity>701</Entity>
' <data>
' <date>
' <day>19</day>
' <month>2</month>
' <year>2021</year>
' </date>
' <time>9:30</time>
' </data>
' </forecast>
' <forecast>
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwhVTFaD469mW9wO194AaABAg. 9gJzxwFcnPU9gORqKw5tW_
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugyb8nmKKoXvcdM58gV4AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwvvXcl1oa79xS7BAV4AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgxvIFArksPprylHXYZ4AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
2 Attachment(s)
Convert Excel data range to XML type text file
In support of this post
https://excelfox.com/forum/showthrea...5355#post15355
_____ Workbook: Sample excel file.xls ( Using Excel 2007 32 bit )
| Row\Col |
A |
B |
C |
D |
E |
F |
G |
H |
I |
|---|
| 1 |
Entity ID |
day |
month |
year |
time |
|
1 |
<-- row number |
|
| 2 |
700 |
19 |
2 |
2021 |
08:00 |
|
2 |
|
|
| 3 |
700 |
19 |
2 |
2021 |
08:30 |
|
3 |
|
|
| 4 |
700 |
20 |
2 |
2021 |
09:00 |
|
4 |
|
|
| 5 |
701 |
19 |
2 |
2021 |
09:30 |
|
5 |
|
|
| 6 |
|
|
|
|
|
|
6 |
|
|
| 7 |
|
2 |
3 |
4 |
5 |
<-- column number |
|
|
|
| 8 |
|
|
|
|
|
|
|
|
|
| 9 |
|
Lr=5 |
|
|
|
|
|
|
|
| 10 |
|
|
arrIn()=Range("A1:E5").Value |
1 |
2 |
3 |
4 |
5 |
6 |
| 11 |
|
|
1 |
Entity ID |
day |
month |
year |
time |
|
| 12 |
|
|
2 |
700 |
19 |
2 |
2021 |
08:00 |
|
| 13 |
|
|
3 |
700 |
19 |
2 |
2021 |
08:30 |
|
| 14 |
|
|
4 |
700 |
20 |
2 |
2021 |
09:00 |
|
| 15 |
|
|
5 |
701 |
19 |
2 |
2021 |
09:30 |
|
| 16 |
|
|
6 |
|
|
|
|
|
|
| 17 |
|
|
|
|
Example: arrIn(5, 1) = 701 |
|
|
|
|
text file output
HTML Code:
<forecast>
<Entity>700</Entity>
<data>
<date>
<day>19/<day>
<month>2</month>
<year>2021</year>
</date>
<time>08:00</time>
<time>08:30</time>
</data>
<data>
<date>
<day>20/<day>
<month>2</month>
<year>2021</year>
</date>
<time>09:00</time>
</data>
</forcast>
<forecast>
<Entity>701</Entity>
<data>
<date>
<day>19/<day>
<month>2</month>
<year>2021</year>
</date>
<time>09:30</time>
</data>
</forcast>
Code:
Option Explicit
'
Sub ExcelToXML()
Rem 1 worksheets data info
Dim Ws1 As Worksheet: Set Ws1 = ThisWorkbook.Worksheets.Item(1)
Dim Lr As Long: Let Lr = Ws1.Range("A" & Rows.Count & "").End(xlUp).Row
Dim arrRng() As Variant: Let arrRng() = Ws1.Range("A1:E" & Lr + 1 & "").Value ' +1 is a bodge to help me not get errors when checking 1 row above my data
Rem 2 Do it
Dim TotalFile As String
Dim Rw As Long: Let Rw = 2 ' Main row count
' #STEP 1 Start
Do While Rw <= Lr ' This keeps us going as long as data is there
Let TotalFile = TotalFile & "<forecast>" & vbCr & vbLf & "<Entity>" & arrRng(Rw, 1) & "</Entity>" & vbCr & vbLf: Debug.Print TotalFile
' # STEP 2 start
Let TotalFile = TotalFile & "<data>" & vbCr & vbLf & "<date>" & vbCr & vbLf & "<day>" & arrRng(Rw, 2) & "/<day>" & vbCr & vbLf & "<month>" & arrRng(Rw, 3) & "</month>" & vbCr & vbLf & "<year>" & arrRng(Rw, 4) & "</year>" & vbCr & vbLf & "</date>" & vbCr & vbLf & "<time>" & Format(arrRng(Rw, 5), "hh" & ":" & "mm") & "</time>" & vbCr & vbLf: Debug.Print TotalFile
' #STEP 3 START
' Check if Entity ID in first row = Entity ID in 2nd row and date in first row = date in 2nd row then repeat STEP 3 for 2nd row and so on
Do While Rw + 1 <= Lr And arrRng(Rw, 1) = arrRng(Rw + 1, 1) And arrRng(Rw, 2) = arrRng(Rw + 1, 2)
Let TotalFile = TotalFile & "<time>" & Format(arrRng(Rw + 1, 5), "hh" & ":" & "mm") & "</time>" & vbCr & vbLf: Debug.Print TotalFile
Let Rw = Rw + 1 ' This brings us to the line we just filled in
Loop
Let TotalFile = TotalFile & "</data>" & vbCr & vbLf: Debug.Print TotalFile
' Chect if Entity ID in first row = Entity ID in 2nd row and date in first row not equals to date in 2nd row then repeat STEP 2 for 2nd row and so on
Do While Rw + 1 <= Lr And arrRng(Rw, 1) = arrRng(Rw + 1, 1) ' And Not arrRng(Rw, 2) = arrRng(Rw + 1, 2)
Let TotalFile = TotalFile & "<data>" & vbCr & vbLf & "<date>" & vbCr & vbLf & "<day>" & arrRng(Rw + 1, 2) & "/<day>" & vbCr & vbLf & "<month>" & arrRng(Rw + 1, 3) & "</month>" & vbCr & vbLf & "<year>" & arrRng(Rw + 1, 4) & "</year>" & vbCr & vbLf & "</date>" & vbCr & vbLf & "<time>" & Format(arrRng(Rw + 1, 5), "hh" & ":" & "mm") & "</time>" & vbCr & vbLf: Debug.Print TotalFile
Let Rw = Rw + 1 ' This brings us to the line we just filled in
Loop
Let TotalFile = TotalFile & "</data>" & vbCr & vbLf: Debug.Print TotalFile
' #STEP 3 END
' STEP 2 END
Let TotalFile = TotalFile & "</forcast>" & vbCr & vbLf: Debug.Print TotalFile
Let Rw = Rw + 1 ' ' This brings us to the next line
' STEP 1 END
Loop ' While Rw <= Lr
Let TotalFile = Replace(TotalFile, "</data>" & vbCr & vbLf & "</data>" & vbCr & vbLf, "</data>" & vbCr & vbLf, 1, -1, vbBinaryCompare): Debug.Print TotalFile ' I end up with a double "</data>" & vbCr & vbLf
Rem 3 Make text file
Dim FileNum2 As Long: Let FileNum2 = FreeFile(0) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
Dim PathAndFileName2 As String
Let PathAndFileName2 = ThisWorkbook.Path & "\" & "XML_Stuff.txt" ' ' CHANGE TO SUIT ' Will be made if not there
Open PathAndFileName2 For Output As #FileNum2
Print #FileNum2, TotalFile ' write out entire text file
Close #FileNum2
End Sub
' <forecast> ' #STEP 1 Start Print #intFile, "<Forecast>"
' <Entity>700</Entity> ' #STEP 1 Start Print #intFile, "<Entity>" & Entity ID & "</Entity>"
' <data> ' #STEP 2 Start Print #intFile, "<Data>"
' <date> ' #STEP 2 Start Print #intFile, "<date>"
' <day>19</day> ' #STEP 2 Start Print #intFile, "<day>" & day &
' <month>2</month> ' #STEP 2 Start "</day><month>" & month & "</month>
' <year>2021</year> ' #STEP 2 Start <year>" & yeear & "</year>"
' </date> ' #STEP 2 Start </date>"
' <time>8:00</time> ' #STEP 3 START Print #intFile, "<time>" & time & "</time>"
' Check if Entity ID in first row = Entity ID in 2nd row
' and date in first row = date in 2nd row then
' <time>8:30</time> ' repeat STEP 3 for 2nd row and so on
' </data> ' #STEP 3 END
' Check if Entity ID in first row = Entity ID in 2nd row
' and date in first row IS NOT = date in 2nd row then'
' repeat STEP ??3?? 2 for 2nd row and so on
' <data>
' <date>
' <day>20</day>
' <month>2</month>
' <year>2021</year>
' </date>
' <time> ??8:00?? 9.00 </time>
' </data>
' </forecast> ' STEP 2 END Print #intFile, "</forecast>"
' If Entity ID is not same as in previous row repeat STEP 1
'
' <forecast>
' <Entity>701</Entity>
' <data>
' <date>
' <day>19</day>
' <month>2</month>
' <year>2021</year>
' </date>
' <time>9:30</time>
' </data>
' </forecast>
' <forecast>
