2

Hello Stackoverflow users,

I am trying to convert files from a non-microsoft file into an excel

I have managed with the following code: 

Sub FastaToExcel()
'
' Macro
'

'
fName = Application.GetOpenFilename()

Workbooks.OpenText Filename:=fName, Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote _
    , ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
    TrailingMinusNumbers:=True

    ActiveWorkbook.SaveAs Filename:="NewName.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWorkbook.Close SaveChanges:=True

End Sub

To get files converted and renamed. This works and a nice shiny NexName.xlsx rolls out.

However, in order to make the naming dynamic I tried the following code based on a post here (VBA Excel file to CSV, keeps CSV filename same as original workbook):

Sub Macro1()
    NewName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 5)
    ActiveWorkbook.SaveAs Filename:="C:\Users\Username\Desktop\" & NewName & ".csv", , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWorkbook.Close SaveChanges:=True
 End Sub

However, this does not work as the "ActiveWorkbook.Name" pulls the name of .xlsm file that has the macro in it to convert the files, and unfortunately does not use the name of non-windows format file of whom I want to keep the name and have it used for the new .xlsx file.

Can anyone help me to tackle this problem? Much appreciated.

[edit] I also tried with the code suggested by @Jeeped (as asked by @skkakkar) in the same topic creating this, but this hangs at ActiveWorkbook.SaveAs line:

Sub FastaToExcel()
'
' Macro
'

'
fName = Application.GetOpenFilename()

Dim myPath As String, myFileName As String

myPath = "C:\Users\" & Environ("USERNAME") & "\Desktop\"
If Not CBool(Len(Dir(myPath, vbDirectory))) Then MkDir Path:=myPath
myFileName = Left(ActiveWorkbook.Name, InStr(1, ActiveWorkbook.Name, ".xl", vbTextCompare) - 1) & ".fasta"

Debug.Print myPath & Chr(92) & myFileName

Workbooks.OpenText Filename:=fName, Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote _
    , ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
    TrailingMinusNumbers:=True

    ActiveWorkbook.SaveAs Filename:=myPath & Chr(92) & myFileName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWorkbook.Close SaveChanges:=True

End Sub
Community
  • 1
  • 1
GMV
  • 67
  • 5
  • in this post @Jeeped has also given his approach for coding this situation by first making myFilename string and the saving taking this in the path. Have you tried this approach. – skkakkar Jun 01 '16 at 12:21
  • @skkakkar I tried to get that to run but that caused an error message (which was non-sensical when I looked it up) for me. I am rather new to this so I may have messed something up. – GMV Jun 01 '16 at 12:23
  • Does your `fName` have an extension to its filename? – PeterT Jun 01 '16 at 13:24
  • @PeterT I am not sure if I follow your question, but all the files I want to convert have the same extention. – GMV Jun 01 '16 at 13:28
  • Then all you have to do is to strip the extension from the original filename and append .xlsx to get your new filename. As an example, if your filename is "abc0123.xxx" : `baseFName = left(fName, len(fName)-4)`, then `NewName = baseFName & ".xlsx"`. Now you can `Activeworkbook.SaveAs Filename:=NewName` – PeterT Jun 01 '16 at 13:35
  • @PeterT Thank you, the most simplest solution seems to be the best. That works lovely - first had a little error by my own fault but that works like a charm. On to the next step in the coding adventure. Hopefully, I can solve that one on my own. – GMV Jun 01 '16 at 13:51

0 Answers0