VBA - How to save in csv one of multiple worksheets in excel file.

LeoM

Member
Local time
Today, 04:50
Joined
Jun 22, 2023
Messages
66
Good afternoon, everyone.
Reading in the messages, I understand that to bypass the issue refer to the Excel importing procedure in Access (who checks the first 20 rows to decide the field type in the target table) is to use a CSV file. Actually, I'm not in the condition to ask to the user to save the file in CSV so I was thinking if there is a way in VBA to:
1. Open the received excel file (which has multiple worksheet),
2. Select one specific worksheet (e.g. Sheet1)
3. Save the new file which will contain only that worksheet in CSV format.
In this way i will then use that CSV to import in Access.
Appreciate your usual and kindly support.
Cheers.
 
Creat a specification and use that.
 
Thanks for the answer but not idea what you mean, appreciate if you clarify it.
Mainly what i need is a msaccess VBA function to do it.
Cheers
 
When you first import an excel sheet, you can get to define what each column type is.
Then you save that specification and specify that on each of your imports.

However, just trying that myself, I do not get the Advanced option. :(
If you know the format, I would just create the table. If you do not know the format, your are still going to be in the same boat?

To do what you are asking I would recreate your steps in Excel, as that has a macro recorder, which will give you the basics for the automation from Access.

Alternatively you can ask ChatGPT or another AI.

Code:
Sub ExportExcelSheetAndImportCSV()
    Dim xlApp As Object
    Dim xlWB As Object
    Dim ws As Object
    Dim csvPath As String
    Dim excelFilePath As String
    Dim worksheetName As String
    Dim importTableName As String

    ' === SET THESE VARIABLES ===
    excelFilePath = "C:\Path\To\Your\Workbook.xlsx"
    worksheetName = "Sheet1"
    csvPath = "C:\Path\To\Exported.csv"
    importTableName = "ImportedData"

    ' === OPEN EXCEL AND WORKBOOK ===
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = False
    Set xlWB = xlApp.Workbooks.Open(excelFilePath)

    ' === EXPORT WORKSHEET TO CSV ===
    Set ws = xlWB.Sheets(worksheetName)
    ws.Copy
    xlApp.ActiveWorkbook.SaveAs Filename:=csvPath, FileFormat:=6 ' xlCSV = 6
    xlApp.ActiveWorkbook.Close False

    ' === CLEAN UP EXCEL ===
    xlWB.Close False
    xlApp.Quit
    Set ws = Nothing
    Set xlWB = Nothing
    Set xlApp = Nothing

    ' === IMPORT CSV TO ACCESS TABLE ===
    DoCmd.TransferText TransferType:=acImportDelim, _
        TableName:=importTableName, _
        Filename:=csvPath, _
        HasFieldNames:=True

    MsgBox "Worksheet exported to CSV and imported into Access table '" & importTableName & "'.", vbInformation
End Sub
Table should exist, but that will get you started.
Tested as far as csv created. Specfication will work with csv file.

 
If you know the format, I would just create the table.
That often does not help. If a column's type is automatically detected and then there is data in a row beyond those read for auto-detection that does not match the detected type, a type conversion error will occur just while Access/ACE is reading the file for import.

I would probably link to the Excel sheet and treat the column headers (hopefully present) as the first data row. This will most likely force the data type detection to treat all as Text.
The run an append query based on the linked Excel Sheet and covert the data types in the query as needed.
 
Similar to Sonic - I use sql and set headers to no and IMEX=1. Which as stated forces everything to text. Disadvantage is column headings become F0, F1, F2 etc and you need to exclude the first row when appending (easy to do with a criteria)

But I don't see the benefit of saving to csv first, just import from excel.

This SQL is the equivalent to having a linked table - you can specify which sheet, (contiguous) columns and (contiguous) rows

Code:
SELECT *
FROM (SELECT * FROM [sheet1$A6:D22] AS xlData IN 'C:\Path\filename.xlsx'[Excel 12.0;HDR=no;IMEX=0;ACCDB=Yes])  AS
XL;

which can then be converted to an append, insert or upsert query (ignoring the first row)

This will run regardless of whether the file is open or not in excel.

If using vba, you could also parse through the destination table to validate datatypes etc before importing
 

Users who are viewing this thread

Back
Top Bottom