How to import Excel file to CSV Step-by-Step

[geoxis]

All,

I have an excel file that I would like from a click of a button import into a CSV file.

Can anyone please help me on this please. I am a newbie so please KIS and step by step.

I have attached the intended file I need to have a buttom that I press to export into CSV.

Thanks,

G

 

[geoxis]

please please any help??

 

[DCrake]

A bit confusing...

Import Excel file to CSV

Are you wanting to convert the Excel file to CSV format?

Do you want to view the data via Access?

Please elaborate.

 

[geoxis]

A bit confusing...

Import Excel file to CSV

Are you wanting to convert the Excel file to CSV format?

Do you want to view the data via Access?

Please elaborate.

Sorry, Yes I would like to be able to convert the excel file that I have attached to CSV format by pressing a button.

Thank you

 

[DCrake]

So let me get this right, you have an Excel file and you do not have it linked to Access but would like to convert the xls to csv? Why not simply open the spreadsheet in Excel and save it as a csv?

If you have it linked Access as an Excel spreadsheet then create a macro using the TransferSpreadsheet command.

Why do you need it in csv format?

David

 

[alfi]

Saving the file as csv is the fastest solution, another will be to loop through all the xsl spreadsheet lines and by code convert them into the CSV , this is a much slower and much more labor intensive approach.

 

[geoxis]

So let me get this right, you have an Excel file and you do not have it linked to Access but would like to convert the xls to csv? Why not simply open the spreadsheet in Excel and save it as a csv?

If you have it linked Access as an Excel spreadsheet then create a macro using the TransferSpreadsheet command.

Why do you need it in csv format?

David

I need it in CSV because I need to import it into access.

Basically I want to create a template in excel that I will give someone to fill in. When this person has finished to fill in, I want this person to convert (by pressing a button) the file in CSV format and then send via email to someone who will import it in access.

I hope this clearer?

Thanks.

 

[DCrake]

Access can quite clearly handle xls files. What makes you think it can't?

You can either Import directly into a table (This will give you a snapshot) or Link the spreadsheet (this will always be live data).

David

 

[geoxis]

thanks - but I need to convert in CSV format, because I need to import in another program also that can only accept CSV formated files.


thanks,

G

 

[geoxis]

This is what I have come up with so far, but I am having trouble:

Private Sub CommandButton1_Click()
Dim FName As Variant
Dim Sep As String
Dim wsSheet As Worksheet
Dim nFileNum As Integer

Sep = InputBox("Enter a single delimiter character ,", _
"Convert to CSV")

For Each wsSheet In Worksheets
wsSheet.Activate
nFileNum = FreeFile
Open wsSheet.Name & ".csv" For Output As #nFileNum
ConvertToCSV CStr(nFileNum), Sep, False
'_
' MsgBox("Do You Want To Convert The Entire Worksheet?", _
' vbYesNo, "Convert to CSV") = vbNo
Close nFileNum
Next wsSheet
End Sub

 

[geoxis]

please any help on this!!!!

 

[jal]

As already suggested, use the TransferSpreadsheet command to import the sheet into an Access table. From Access there are a couple of different ways to export the table to CSV, for instance maybe something like this:

DoCmd.TransferText acExportDelim, , "SourceTable", "C:\test.csv", True