Quelle est la meilleure façon de stocker un grand file text (max 10 Mb) dans un file Excel?
J'ai quelques exigences:
Il doit être embedded afin que le file excel puisse être déplacé et envoyé à un autre ordinateur et tous les files text suivent.
Il faut le faire à partir d'une macro.
Et une macro doit pouvoir lire le contenu du file après son intégration.
J'ai déjà essayé de le stocker en brisant le text en plusieurs morceaux assez petits pour entrer dans une cellule (~ 32 000 caractères), mais cela n'a pas fonctionné. Après que ma macro a inséré les premiers 150 000 caractères, cela m'a donné une erreur de «memory insuffisante».
Je me souviens d'avoir vu une page Web avec quelques options pour cela, mais je ne peux plus le find. Toutes les suggestions sont les bienvenues. Je vais les essayer si vous ne savez pas si cela fonctionne ou non.
Exemple très simple de CustomXMLPart:
Sub CustomTextTester() Dim cxp1 As CustomXMLPart, cxp2 As CustomXMLPart Dim txt As Ssortingng 'read file content txt = CreateObject("scripting.filesystemobject").opentextfile( _ "C:\_Stuff\test.txt").readall() 'Add a custom XML part with that content Set cxp1 = ThisWorkbook.CustomXMLParts.Add("<myXMLPart><content><![CDATA[" & txt _ & "]]></content></myXMLPart>") Debug.Print cxp1.SelectSingleNode("myXMLPart/content").FirstChild.NodeValue End Sub
Il serait préférable d'save simplement le file .txt à côté du file Excel et de faire en sorte que la macro tire le text tel que nécessaire de ce dossier. Pour en savoir plus sur l'import de files, voir ceci:
Garder le file .txt dans le file Excel lui-même n'est pas nécessaire et risque de rendre plus difficile le transfert de files à plus long terme. Par exemple, si vous ne pouvez pas envoyer par e-mail un file de plus de 10 Mo, vous pouvez simplement casser votre file .txt en deux et par courrier électronique séparément – en utilisant une macro qui charge le text dans Excel localement.
Peut-être que cela aidera:
Option Explicit Public Sub embedFile() 'C:\test.txt size: 18.5 Mb addFile "C:\test.txt", "C:\test.xlsb" 'Result: C:\test.xlsb size: 1 Mb End Sub Public Sub addFile(ByVal fullTextFileName As Ssortingng, resultFile As Ssortingng) With Sheet1 .OLEObjects.Add Filename:=fullTextFileName, _ Link:=False, _ DisplayAsIcon:=False .SaveAs Filename:=resultFile, _ FileFormat:=xlExcel12, _ CreateBackup:=False End With End Sub
Option ExplicitOption Explicit Public Sub embedFile() 'C:\test.txt size: 18.5 Mb addFile "C:\test.txt", "C:\test.xlsb" 'Result: C:\test.xlsb size: 1 Mb End Sub Public Sub addFile(ByVal fullTextFileName As Ssortingng, resultFile As Ssortingng) With Sheet1 .OLEObjects.Add Filename:=fullTextFileName, _ Link:=False, _ DisplayAsIcon:=False .SaveAs Filename:=resultFile, _ FileFormat:=xlExcel12, _ CreateBackup:=False End With End Sub
Public Sub embedFile ()Option Explicit Public Sub embedFile() 'C:\test.txt size: 18.5 Mb addFile "C:\test.txt", "C:\test.xlsb" 'Result: C:\test.xlsb size: 1 Mb End Sub Public Sub addFile(ByVal fullTextFileName As Ssortingng, resultFile As Ssortingng) With Sheet1 .OLEObjects.Add Filename:=fullTextFileName, _ Link:=False, _ DisplayAsIcon:=False .SaveAs Filename:=resultFile, _ FileFormat:=xlExcel12, _ CreateBackup:=False End With End Sub
'C: \ test.txt taille: 18.5 MoOption Explicit Public Sub embedFile() 'C:\test.txt size: 18.5 Mb addFile "C:\test.txt", "C:\test.xlsb" 'Result: C:\test.xlsb size: 1 Mb End Sub Public Sub addFile(ByVal fullTextFileName As Ssortingng, resultFile As Ssortingng) With Sheet1 .OLEObjects.Add Filename:=fullTextFileName, _ Link:=False, _ DisplayAsIcon:=False .SaveAs Filename:=resultFile, _ FileFormat:=xlExcel12, _ CreateBackup:=False End With End Sub
addFile "C: \ test.txt", "C: \ test.xlsb"Option Explicit Public Sub embedFile() 'C:\test.txt size: 18.5 Mb addFile "C:\test.txt", "C:\test.xlsb" 'Result: C:\test.xlsb size: 1 Mb End Sub Public Sub addFile(ByVal fullTextFileName As Ssortingng, resultFile As Ssortingng) With Sheet1 .OLEObjects.Add Filename:=fullTextFileName, _ Link:=False, _ DisplayAsIcon:=False .SaveAs Filename:=resultFile, _ FileFormat:=xlExcel12, _ CreateBackup:=False End With End Sub
'Résultat: C: \ test.xlsb taille: 1 MoOption Explicit Public Sub embedFile() 'C:\test.txt size: 18.5 Mb addFile "C:\test.txt", "C:\test.xlsb" 'Result: C:\test.xlsb size: 1 Mb End Sub Public Sub addFile(ByVal fullTextFileName As Ssortingng, resultFile As Ssortingng) With Sheet1 .OLEObjects.Add Filename:=fullTextFileName, _ Link:=False, _ DisplayAsIcon:=False .SaveAs Filename:=resultFile, _ FileFormat:=xlExcel12, _ CreateBackup:=False End With End Sub
Public Sub addFile (ByVal fullTextFileName As Ssortingng, resultFile As Ssortingng)Option Explicit Public Sub embedFile() 'C:\test.txt size: 18.5 Mb addFile "C:\test.txt", "C:\test.xlsb" 'Result: C:\test.xlsb size: 1 Mb End Sub Public Sub addFile(ByVal fullTextFileName As Ssortingng, resultFile As Ssortingng) With Sheet1 .OLEObjects.Add Filename:=fullTextFileName, _ Link:=False, _ DisplayAsIcon:=False .SaveAs Filename:=resultFile, _ FileFormat:=xlExcel12, _ CreateBackup:=False End With End Sub
Avec Sheet1Option Explicit Public Sub embedFile() 'C:\test.txt size: 18.5 Mb addFile "C:\test.txt", "C:\test.xlsb" 'Result: C:\test.xlsb size: 1 Mb End Sub Public Sub addFile(ByVal fullTextFileName As Ssortingng, resultFile As Ssortingng) With Sheet1 .OLEObjects.Add Filename:=fullTextFileName, _ Link:=False, _ DisplayAsIcon:=False .SaveAs Filename:=resultFile, _ FileFormat:=xlExcel12, _ CreateBackup:=False End With End Sub
.OLEObjects.Add Nom du file: = fullTextFileName, _Option Explicit Public Sub embedFile() 'C:\test.txt size: 18.5 Mb addFile "C:\test.txt", "C:\test.xlsb" 'Result: C:\test.xlsb size: 1 Mb End Sub Public Sub addFile(ByVal fullTextFileName As Ssortingng, resultFile As Ssortingng) With Sheet1 .OLEObjects.Add Filename:=fullTextFileName, _ Link:=False, _ DisplayAsIcon:=False .SaveAs Filename:=resultFile, _ FileFormat:=xlExcel12, _ CreateBackup:=False End With End Sub
Lien: = False, _Option Explicit Public Sub embedFile() 'C:\test.txt size: 18.5 Mb addFile "C:\test.txt", "C:\test.xlsb" 'Result: C:\test.xlsb size: 1 Mb End Sub Public Sub addFile(ByVal fullTextFileName As Ssortingng, resultFile As Ssortingng) With Sheet1 .OLEObjects.Add Filename:=fullTextFileName, _ Link:=False, _ DisplayAsIcon:=False .SaveAs Filename:=resultFile, _ FileFormat:=xlExcel12, _ CreateBackup:=False End With End Sub
DisplayAsIcon: = FalseOption Explicit Public Sub embedFile() 'C:\test.txt size: 18.5 Mb addFile "C:\test.txt", "C:\test.xlsb" 'Result: C:\test.xlsb size: 1 Mb End Sub Public Sub addFile(ByVal fullTextFileName As Ssortingng, resultFile As Ssortingng) With Sheet1 .OLEObjects.Add Filename:=fullTextFileName, _ Link:=False, _ DisplayAsIcon:=False .SaveAs Filename:=resultFile, _ FileFormat:=xlExcel12, _ CreateBackup:=False End With End Sub
.SaveAs Filename: = resultFile, _Option Explicit Public Sub embedFile() 'C:\test.txt size: 18.5 Mb addFile "C:\test.txt", "C:\test.xlsb" 'Result: C:\test.xlsb size: 1 Mb End Sub Public Sub addFile(ByVal fullTextFileName As Ssortingng, resultFile As Ssortingng) With Sheet1 .OLEObjects.Add Filename:=fullTextFileName, _ Link:=False, _ DisplayAsIcon:=False .SaveAs Filename:=resultFile, _ FileFormat:=xlExcel12, _ CreateBackup:=False End With End Sub
FileFormat: = xlExcel12, _Option Explicit Public Sub embedFile() 'C:\test.txt size: 18.5 Mb addFile "C:\test.txt", "C:\test.xlsb" 'Result: C:\test.xlsb size: 1 Mb End Sub Public Sub addFile(ByVal fullTextFileName As Ssortingng, resultFile As Ssortingng) With Sheet1 .OLEObjects.Add Filename:=fullTextFileName, _ Link:=False, _ DisplayAsIcon:=False .SaveAs Filename:=resultFile, _ FileFormat:=xlExcel12, _ CreateBackup:=False End With End Sub
CreateBackup: = FalseOption Explicit Public Sub embedFile() 'C:\test.txt size: 18.5 Mb addFile "C:\test.txt", "C:\test.xlsb" 'Result: C:\test.xlsb size: 1 Mb End Sub Public Sub addFile(ByVal fullTextFileName As Ssortingng, resultFile As Ssortingng) With Sheet1 .OLEObjects.Add Filename:=fullTextFileName, _ Link:=False, _ DisplayAsIcon:=False .SaveAs Filename:=resultFile, _ FileFormat:=xlExcel12, _ CreateBackup:=False End With End Sub
(xlsb est un file Excel normal enregistré en format binary, il peut également contenir des modules VBA)