CODEWITHSUNDEEP
ms-accessvba
blaufer
blaufer

Reputation: 119

Insert Column into Imported Table

I have code to import a table from Excel into Access but I want to be able to add a column with a header to the table. This column would then contain the number that was chosen as "x" for all rows. This should be explained by the attached code.

Private Sub Import_Click()

    Dim dlg As Object
    Set dlg = Application.FileDialog(3)
    x = InputBox("Harvest Data Number:")

    With dlg
        .Title = "Select the Excel file to import"
        .AllowMultiSelect = False
        .Filters.Clear
        .Filters.Add "Excel Files", "*.xlsx", 1
        .Filters.Add "All Files", "*.*", 2

        If .Show = -1 Then
            StrFileName = .SelectedItems(1)
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Harvest " & x & " Peptide Data", StrFileName, True, "DENOVO sorted by AMP!A21:Z"
        End If

    End With

End Sub

Upvotes: 0

Views: 397

Answers (1)

HansUp
HansUp

Reputation: 97131

Since you want to populate a column which isn't present in the sheet, consider an append query instead of TransferSpreadsheet. You can use a query parameter to get the value of x (Harvest Data Number).

This example pulls from Sheet1$ in a workbook named temp.xls and stores those data in a table named tblDestination. Note the source and destination field names can be different.

INSERT INTO tblDestination ([Harvest Data Number], fld2, fld3, fld4)
SELECT [x] AS [Harvest Data Number], s1.[Seq#], s1.Name, s1.Location
FROM [Excel 8.0;HDR=YES;IMEX=2;DATABASE=C:\SomeFolder\temp.xls].[Sheet1$] AS s1;

Upvotes: 1

Related Questions