Macro to convert Text files into Excel file

[Kazdima]

Hello,

I would appreciate aveone's help to write for me a Macro to Convert a Text file into Excel file.
I will send a Text file and an example of an Excel format file to those who can help me.

Thank you,

Dan

 

[Joe4]

Re: Macro to convert Text file into Excel file

You should be able to do this yourself with the Macro Recorder.
If you turn on the Macro Recorder, and manually go through the steps needed for the conversion, it should record most of the VBA code that you need.
If you need to clean it up a little, and make it more dynamic, you can post the recorded code here and let us know what kind of adjustments you need.

 

[Kazdima]

Re: Macro to convert Text file into Excel file

OK. Thank you.

 

[Kazdima]

Re: Macro to convert Text file into Excel file

Hi,
can you make this Macro more robust?-)
=============================================

Sub Convert()
'
' Convert Macro
'


'
Workbooks.OpenText Filename:="N:\SDSBUDG", Origin:=437, StartRow:=1, _
DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(1, 2), Array(3, 9), _
Array(6, 2), Array(9, 9), Array(12, 2), Array(13, 9), Array(16, 2), Array(19, 9), Array(22, _
2), Array(26, 9), Array(30, 2), Array(33, 9), Array(36, 2), Array(66, 9), Array(68, 1), _
Array(81, 9), Array(82, 1), Array(95, 9), Array(96, 1), Array(109, 9), Array(110, 1), Array _
(123, 9), Array(124, 1), Array(138, 1)), TrailingMinusNumbers:=True
ActiveWorkbook.SaveAs Filename:="N:\SDSBUDG.xlsm", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Cells.Select
Selection.Copy
Windows("SDSBUDG 2017-18 Download.xlsm").Activate
Cells.Select
ActiveSheet.Paste
Range("G10").Select
Columns("G:G").EntireColumn.AutoFit
Columns("H:H").ColumnWidth = 8.18
Columns("H:H").EntireColumn.AutoFit
Columns("I:I").EntireColumn.AutoFit
Columns("J:J").EntireColumn.AutoFit
Columns("K:K").EntireColumn.AutoFit
Application.CutCopyMode = False
ActiveWorkbook.Save
End Sub

 

[Joe4]

Re: Macro to convert Text file into Excel file

Not sure what you mean by making it more "robust". What exactly is it that you want to add to it?
You can clean it up a little, like this:

Sub Convert()
'
' Convert Macro
'

    Workbooks.OpenText Filename:="N:\SDSBUDG", Origin:=437, StartRow:=1, _
        DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(1, 2), Array(3, 9), _
        Array(6, 2), Array(9, 9), Array(12, 2), Array(13, 9), Array(16, 2), Array(19, 9), Array(22, _
        2), Array(26, 9), Array(30, 2), Array(33, 9), Array(36, 2), Array(66, 9), Array(68, 1), _
        Array(81, 9), Array(82, 1), Array(95, 9), Array(96, 1), Array(109, 9), Array(110, 1), Array _
        (123, 9), Array(124, 1), Array(138, 1)), TrailingMinusNumbers:=True

    ActiveWorkbook.SaveAs Filename:="N:\SDSBUDG.xlsm", FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

    Cells.Copy
    Windows("SDSBUDG 2017-18 Download.xlsm").Activate
    Cells.Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

    Columns("G:K").EntireColumn.AutoFit
    
    ActiveWorkbook.Save
    
End Sub

 

[Kazdima]

Re: Macro to convert Text file into Excel file

this is what you suggested me:
If you need to clean it up a little, and make it more dynamic,...

So, can you clean it and make it more dynamic?

 

[Joe4]

Re: Macro to convert Text file into Excel file

So, can you clean it and make it more dynamic?

I did clean it up.

The only possible thing left to make it more dynamic is if you are not going to be opening the same file name each time, and want the ability to browse for the file (instead of having the file name hard-coded in your code). Do you have that situation where that would be beneficial to you?

 

[Kazdima]

Re: Macro to convert Text file into Excel file

Thank you, Joe4.
I will try it later and let you know how it works.
I have one txt file, which should be converted into Excel format, and after conversion should be copied into another Excel workbook into an empty sheet.
I was opening an Excel workbook where one of sheets is empty, and from there recorded a Macro how I did conversion from txt file into Excel and copied to Excel empty sheet a converted file.

A txt file will be updated from my data base each time, when I need it. Unfortunately the updating cannot be automated.

I guess, any way I have to open an Excel file with Macro to run my Macro... I hope I understood correctly your question?

 

[Joe4]

Re: Macro to convert Text file into Excel file

I was referring to the Text file you are converting. Is it named the same thing every time?
If not, we can have the Macro open the file browser, so you pick the file, and the macro does the rest.
If it is named the same thing every time, then you can leave it as it is.

 

[Kazdima]

Re: Macro to convert Text file into Excel file

HI,

Yes, TXT file has the same name even after each time being updated, because this name is given by the accounting system from which is is downloaded.
Thank you for your help.