Solved

Access 2007 csv to Excel

Posted on 2011-02-17
4
469 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
[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
  • 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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

734 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