Solved

Access 2007 csv to Excel

Posted on 2011-02-17
4
443 Views
Last Modified: 2012-06-27
I have an access app that outputs all the rows of a database table. I had to output to csv as excel has a row limitation. The app then sends the file as an email to various people, they then have to save as XL to add formatting etc.
Does anyone know how to convert the csv into xls/xlsx using VBA?
0
Comment
Question by:HKFuey
  • 2
4 Comments
 
LVL 9

Expert Comment

by:borki
ID: 34914560
What will the user run, once they receive the .csv file? Default would be Excel, is that your preferred solution?
0
 

Author Comment

by:HKFuey
ID: 34914911
Hi Borki, Ideally they need to use Excell (to do stock calculations etc.)
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 34915510
try this codes


Sub saveasXLS()
Dim xlObj As Object, xlFile As String, csvFile
csvFile = "C:\xxx.csv"
xlFile = "C:\xxx.xls"
Set xlObj = CreateObject("excel.Application")
    xlObj.Workbooks.Open (csvFile)
    xlObj.activeworkbook.saveas xlFile, FileFormat:=43, CreateBackup:=False
    xlObj.activeworkbook.Saved = True
    xlObj.Quit
End Sub

for xlsx use FileFormat:=51

if you have problem with FileFormat:=43, use,   -4143  or  56



0
 

Author Closing Comment

by:HKFuey
ID: 34917867
Hi Capricorn1, that's great, works perfect. Thanks!

I used  -4143 and also:

if xlfile>"" then kill xlfile

Thanks again.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

867 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now