Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1137
  • Last Modified:

Excel auto load CSV, save XLS, close?

Any know if it's possible to open a CSV file in Excel, save it as a XSL file and automatically close?  Maybe from the command line?
0
ChrisCobb
Asked:
ChrisCobb
  • 5
  • 3
1 Solution
 
bulletheadCommented:
This should get you started:

Sub MakeXL()
   
   Dim strfilename As String
   strfilename = "c:\temp\Filename.csv"
   Dim strnewfile As String
   Workbooks.Open Filename:=strfilename
   strnewfile = Left(strfilename, Len(strfilename) - 3) & "xls"
   ActiveWorkbook.SaveAs strnewfile, xlNormal
   Application.Quit

End Sub

I'll think about the prospect of doing it from the comand line.
0
 
ChrisCobbAuthor Commented:
Is that VB?
0
 
bulletheadCommented:
That code is adapted from my solution to:

http://www.experts-exchange.com/Applications/Q_20540799.html

which was a little VB app that does more or less what you describe.  You might want to have a look at that.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
bulletheadCommented:
Sorry, to clarify, not quite - as it stands it is VBA, which is what Excel uses as its macro language now.
0
 
ChrisCobbAuthor Commented:
Your little VB app from your previous solution (pasted below) look perfect.  Unfortunatley when I compile (Start with full compile) it gives a compile error "User-defined type not defined" on the first Dim line in the MakeXL sub.  Am I missing a plug-in/library or something?  (Yes, I'm showing my lack of any VB knowledge here!).

The code:

Sub Main()

Dim strfile As String

If Right(CStr(Command), 3) = "csv" Then
   Call MakeXL(Command())
   Else: strfile = InputBox("Filepath to convert", "Path")
   If Right(strfile, 3) <> "csv" Then
       MsgBox "Unknown file type - closing"
       Exit Sub
   End If
   Call MakeXL(strfile)
End If

End Sub

Sub MakeXL(strfilename As String)
   Dim objExcel As Excel.Application
   Set objExcel = New Excel.Application
   
   Dim strnewfile As String
   objExcel.Workbooks.Open filename:=strfilename
   strnewfile = Left(strfilename, Len(strfilename) - 3) & "xls"
   objExcel.ActiveWorkbook.SaveAs strnewfile, xlNormal
   objExcel.Quit
   set objexcel = Nothing
End Sub
0
 
bulletheadCommented:
You need to go to Project\References and tick the box for the Excel type library.
0
 
ChrisCobbAuthor Commented:
That works great - thank you!
0
 
bulletheadCommented:
It's a pleasure - thanks for the points and the grade.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now