?
Solved

Excel auto load CSV, save XLS, close?

Posted on 2003-03-20
8
Medium Priority
?
1,127 Views
Last Modified: 2008-01-09
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
Comment
Question by:ChrisCobb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 5

Expert Comment

by:bullethead
ID: 8174586
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
 

Author Comment

by:ChrisCobb
ID: 8174611
Is that VB?
0
 
LVL 5

Expert Comment

by:bullethead
ID: 8174613
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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 5

Expert Comment

by:bullethead
ID: 8174626
Sorry, to clarify, not quite - as it stands it is VBA, which is what Excel uses as its macro language now.
0
 

Author Comment

by:ChrisCobb
ID: 8175123
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
 
LVL 5

Accepted Solution

by:
bullethead earned 200 total points
ID: 8175230
You need to go to Project\References and tick the box for the Excel type library.
0
 

Author Comment

by:ChrisCobb
ID: 8180201
That works great - thank you!
0
 
LVL 5

Expert Comment

by:bullethead
ID: 8196173
It's a pleasure - thanks for the points and the grade.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
This video demonstrates how to use each tool, their shortcuts, where and when to use them, and how to use the keyboard to improve workflow.
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question