open csv file with excel

[focus10]

for opening a csv file i use
StartDoc = ShellExecute(Application.hWndAccessApp, "Open", "c:\a.csv", "", "C:", SW_SHOWNORMAL) line

what can i add to the above line for opening the file with excel application?

thanks

 

[arnelgp]

you can use:

Application.FollowHyperLink "c:\a.csv"

Or:


Dim xlApp As Object
Dim xlWB As Object
Set xlApp = CreateObject("Excel.Application")
Set xlWB = xlApp.Workbooks.Open("c:\a.csv")
xlApp.Visible = True

 

[focus10]

i know that i can write the excel.exe in my line like:
StartDoc = ShellExecute(Application.hWndAccessApp, "Open", "C:\Program Files\Microsoft Office\Office14\excel.exe" "c:\a.csv", "", "C:", SW_SHOWNORMAL)
but in other cases it can be another folder.
so what i need is the default excel application

 

[arnelgp]

just remove:

Set xlWB=xlApp.WorkBooks.Open(...

then modify:


Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible=True
xlApp.FindFile

 

[moke123]

dev ashish's shellexecute module will open any file with its registered default program

http://access.mvps.org/access/api/api0018.htm

 

[Pat Hartman]

Excel isn't as smart as it thinks it is and opening a .csv file with a double-click (which is what the suggested methods mimic), you have to be certain that you don't have any zip codes or other codes that begin with leading zeros because Excel assumes they are numbers and numbers don't have leading zeros so my zip is converted from 06614 to 6614. And then there is the problem with long numbers. Many applications use long numeric strings as record IDs. Those IDs may only contain numbers (such as CC numbers and SSN) but they are codes and codes need to be defined as text. Excel converts these to scientific notation and that causes you to loose the true value of the ID.

 

[focus10]

dev ashish's shellexecute module will open any file with its registered default program

http://access.mvps.org/access/api/api0018.htm

problem with this solution that in some cases there is no registered default program for csv.
so i search for a solution that will force opening the csv file with excel with the shellexecute module.
any excel version that is exists in the computer

 

[isladogs]

problem with this solution that in some cases there is no registered default program for csv.
so i search for a solution that will force opening the csv file with excel with the shellexecute module.
any excel version that is exists in the computer

That surprises me as I thought Excel automatically made itself the default for csv files.

If that's not the case, its easy enough to set Excel or another program such as Notepad as the default

 

[moke123]

That surprises me as I thought Excel automatically made itself the default for csv files.

Thats what I thought also.
It's also possible to set the file association with vba and registry changes.

http://www.freevbcode.com/ShowCode.asp?ID=2799

 

[Pat Hartman]

If you'll look back at my first comment, you will see why some companies remove Excel as the default for opening .csv files. To properly open a csv file in Excel, you need to use the File Open ribbon. That give you a dialog similar to what you get with Access EXCEPT that you don't have the option to save it so it is a real PITA to have to specify data types each time. However, if you know there are no problem data types in the input, you can simply accept the types that Excel assumes.

 

[isladogs]

Excel isn't as smart as it thinks it is and opening a .csv file with a double-click (which is what the suggested methods mimic), you have to be certain that you don't have any zip codes or other codes that begin with leading zeros because Excel assumes they are numbers and numbers don't have leading zeros so my zip is converted from 06614 to 6614. And then there is the problem with long numbers. Many applications use long numeric strings as record IDs. Those IDs may only contain numbers (such as CC numbers and SSN) but they are codes and codes need to be defined as text. Excel converts these to scientific notation and that causes you to loose the true value of the ID.

Pat

I understand what you're saying but I'm not sure I totally agree
The screenshot is a CSV file opened in Excel by double clicking the file name
It contains phone numbers starting with '0' & they aren't truncated
I've redacted them (badly) though the data isn't real

However, this issue does apply when importing CSV data into Access
Therefore I always specify the datatype to prevent e.g. phone numbers being read as numbers and losing the leading zero

In this case CStr(Tel.) ...and yes I know that's a bad field name but unfortunately that's the output from a schools management database that I have to work with. I export CSV files of staff & student data every night and then import into my own Access databases.

 

[static]

^ Depends how you export the data.
If strings are enclosed in quotes they'll most likely be imported as strings. But that's often not the case.

I can't think of any reason why you'd want to open a csv file in Excel from Access. Summats up 'ere.

 

[isladogs]

^ Depends how you export the data.
If strings are enclosed in quotes they'll most likely be imported as strings. But that's often not the case.

True.
However I have had issues importing some fields with 'ambiguous' datatypes from CSV files into Access. I do this as an automated & unattended routine every night & I need to ensure it works correctly.
That's why I specify the datatype if there is any risk of it being misunderstood

I can't think of any reason why you'd want to open a csv file in Excel from Access. Summats up 'ere.

Not sure anyone has mentioned opening the CSV file in Excel from Access. If they have I've missed it.

 

[static]

Well, he posted in an Access forum and the code must be running from somewhere.

 

[Mark_]

Focus10,

Did you try arnelgp solution? He's very good with this type of thing and is a highly useful source for assistance.