file import - table problem

[Armitage2k]

Hello!

I am using the following code to import data from a closed XL spreadsheet and import it into a table in my database.

Sub GetDataFromClosedWorkbook()
Dim wb As Workbook
Dim ws As Worksheet
Dim db As Database, rec As Recordset
Dim Filename As Object
Dim Selection As String

Set db = CurrentDb
Set rec = db.OpenRecordset("VIP")
Set Filename = Application.FileDialog(msoFileDialogFilePicker)
    With Filename
    .InitialView = msoFileDialogViewDetails:
    .InitialFileName = "C:\test\":
    .Filters.Clear:
    .Filters.Add "Pick .xls File", "*.xls", 1:
    .ButtonName = "Import file":
    .Title = "Search for Database_Import.xls file"

    ' If no file is selected, close the sub, else keep going
        If .Show = -1 Then
            Selection = .SelectedItems(1)
        Else:
            Exit Sub
        End If
    End With
    
Set wb = Workbooks.Open(Selection, True, True)
Set ws = wb.Worksheets("VIP") ' define which worksheet shall be imported
    
    ' open the source workbook, read only
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "VIP2", Selection, True
    
    ' close the source workbook without saving any changes
    wb.Close False
    
    Set wb = Nothing ' free memory
    rec.Close
End Sub

the problem with the code is that it imports from the .xls as wished, but it creates me around 300 empty entries before showing me the first entry which is entered in row 2 on the xl spreadsheet.
Solution to this would be a way to check for empty fields and if yes, delete the whole line. (Eg. if field "Profile ID" is empty, delete whole line)

Further, I later want to import the data directly into an existing table, but i would need Access to understand that if the entry in the field "Profile ID" exists already, that it shall replace the existing one. However, no double existing entries may exist in the database (measured by the Profile ID).

Maybe someone can give me a hand on this since I dont know how to do this, respectively where to start.
Thanks for the help!
A2k

 

[gemma-the-husky]

you are better doing what you are doing

import to a temporary table

delete blank rows

validate the data

and only then

add new rows to permanent tables
update existing rows

-----------
note that in general you get more control importing a csv. so if you have say, an external system exporting a csv file, you are better to import tihs than put it through excel

1) you get more control importing a csv
2) putting stuff through excel changes some of the data

 

[Armitage2k]

well, thanks for the advice, but I need this for people who are totally unfamiliar with access. Actually, I am already glad if they manage to get the computer started and shut down without damaging something...

generally, if I want to keep my way, how could I make this fully automatically? with VBA code?

thanks,
A2k

 

[gemma-the-husky]

yes you can do all that with code


import
.. .docmd.transfertext (for a csv)
or docmd.transferspreadsheet (for a xl)

probably with a browse function to pick the file


validate
a series of tests - generally queries that should return a known answer. eg, test for duplicates on a certain field, or blanks in certain fields
even sheck that all ther correct columns exist in the imported table

transfer
an append query to add the data to the main table, only if the validations passed.


takes a bit of time to get it working, but useful, because then the layout/style is completely reuseable for other apps - you just have to change the specific bits to suit the app.