Save .txt file as .xlsx file

[Infinite]

As the title says, I am trying to save a .txt file as a .xlsx file. I have looked around a lot before asking this, and I cant find anything that is working for me.

The path for the file would look like this:

Dim FilePath As String


FilePath = Dir("C:\Users\Joshua\Downloads\!Import Sales From Online\*.txt")

So I just need to figure out how to save that as a .xlsx file. Or even a .csv file.

Thanks for the help!

EDIT: I want it to be saved using VBA from Access. Sorry about not being clearer when I first posted.

 

[James Deckert]

How is the .txt file formatted?
Can you show a piece of it?

 

[Infinite]

Sure! Its in the attached image:

 

[ypma]

"So I just need to figure out how to save that as a .xlsx file. Or even a .csv file."

Using Excel 2010 you can inport txt files .
Home button From text get external data

This should give you a xlsx file containing your txt data

Hope this is of some use to you

Regards Ypma

 

[Infinite]

I want it to be saved using VBA from Access. Sorry about not being clearer when I first posted.

 

[Minty]

Access can only create a xlsx from data, not an external text file. You would have to import it to a staging table then export it.

 

[Infinite]

Is that possible with VBA?

 

[sxschech]

This is code I use to import csv, txt and xls(x) files. I stripped out the other parts particular to my application, so hopefully it will work for your situation, you may need to modify it slightly. The code will take a file open it in excel, format all the cols to text and then save it in current xlsx format, then will import in using the transfer spreadsheet.

Place this code in your form. stImport will be the name of the file you want to import, can be text or obtain using a combobox or a file dialog picker. stTableName will be the name to give the access table once file is imported.

Call ExcelToText(stimport)
stimport = Replace(stimport, ".xl", "txt.xl")
stimport = Replace(stimport, ".csv", "txt.xlsx")
stimport = Replace(stimport, ".txt", "txt.xlsx")
stSheetType = globalintExcelVer
DoCmd.TransferSpreadsheet acImport, stSheetType, stTableName, stimport, True
Call DeleteFile(stimport)

In a module put this at the top

Public globalintExcelVer As Integer

In same module where convenient, put

Public Sub ExcelToText(ByVal stfilepath As String)
'http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28277710.html
'http://www.developark.com/1529_17747527/
'http://www.mrexcel.com/forum/excel-questions/235867-using-column-numbers-define-range-visual-basic-applications.html
'Change the formatting of an excel file to text for all used cols (last col)
'Save as to another name (filename + txt.xlsx)
'Changed so no longer needs to set reference to excel
'Found that last col wasn't getting all data, switched to .cells without
'range in order to capture entire sheet.
'Changed to convert all reformatted files into Excel 2007-2013 format xlsx.
'201506??

    Dim objapp As Object
    Dim wb As Object
    Dim lastCol As Long
    Dim ExcelVersion As Long
    
    On Error Resume Next
    Set objapp = CreateObject("Excel.Application")
    objapp.Visible = True
    
    If Dir(stfilepath) Then
        Set wb = objapp.workbooks.Open(stfilepath, True, False)
    End If
    With wb.Sheets(1)
        .cells.NumberFormat = "@"
         .Name.Delete
    End With
        'wb.Sheets(1).Range("A:BB").NumberFormat = "@"
        'http://www.rondebruin.nl/win/s5/win001.htm
        'filefomatstuff 20150405
    wb.SaveAs FilePath(stfilepath) & FileNameNoExt(stfilepath) & "txt.xlsx", FileFormat:=51
    ExcelVersion = wb.FileFormat
    wb.Close 'savechanges:=False
  
        objapp.Quit

    Set objapp = Nothing
    Select Case ExcelVersion
        Case 39
            ExcelVersion = 5 'excel7
        Case 50, 51
            ExcelVersion = 9 'excel12 (2007-2013, xlsx)
        Case 56
            ExcelVersion = 8 'excel8 (97-2003 format in Excel 2007-2013, xls)
    End Select
    globalintExcelVer = ExcelVersion
End Sub

Public Sub DeleteFile(Killfile As String)
'http://word.mvps.org/faqs/macrosvba/DeleteFiles.htm
'20150308
    If Len(Dir$(Killfile)) > 0 Then
        SetAttr Killfile, vbNormal
        Kill Killfile
    End If
End Sub

 

[Infinite]

Is there any way you can upload a test database? Im not sure where to put my path and such. It does look like it should work though