Automatically Update Delimited/Comma Seperated File Options

[bony_tony]

Hi,
I have an exported file saved which has all it's values in Column A,
with each value seperated by ". If I open the file manually, I can
go
through the steps to seperated the values, which is fine. However, I
have a sub which is going to open my delimited file overnight, so I
would like to automatically amend the file, seperating the values.

I have Application.Dialogs(xlDialogTextToColumns).Show, but it
requires user input, which is a no-no.


Cheers
Tony

 

[Keith74]

Have you tried recording a macro while you go through the process
manually, then adapt the code?

 

[CoRrRan]

Hi,
I have an exported file saved which has all it's values in Column A,
with each value seperated by ". If I open the file manually, I can
go
through the steps to seperated the values, which is fine. However, I
have a sub which is going to open my delimited file overnight, so I
would like to automatically amend the file, seperating the values.

I have Application.Dialogs(xlDialogTextToColumns).Show, but it
requires user input, which is a no-no.


Cheers
Tony

Do you have a .txt (or similar file) with delimited content, based on
the "." character (or """-character) and you want to import this file
during a process that runs at night, without user input required?

I would suggest you should write a parser for the file using the
FileSystemObject and the Split-function.

******************
Sub ParseInputFile()

Dim FSO as Scripting.FileSystemObject
Dim TS as Scripting.Textstream

Dim sData As String, sArr() As String

Dim i As Integer

Set FSO = New Scripting.FileSystemObject
Set TS = FSO.OpenTextfile("C:\Temp\file.txt")

Do
sData = TS.ReadLine
If sData <> "" Then
sArr = Split(sData,".") ' (or sArr = Split(sData,""""))
Range("A1").Offset(i,0).Resize(1,UBound(sArr)) = _
Application.Transpose(sArr)
i = i + 1
End If
Loop While TS.AtEndOfStream <> True

Set TS = Nothing
Set FSO = Nothing

End Sub
******************

I'm not sure whether above code works directly, but I hope you can adapt
it to your needs.

Make sure you have a Reference to "Microsoft Scripting Runtime" (VBE -->
Tools --> References --> select "Microsoft Scripting Runtime" --> close
with "OK")

CoRrRan