Command button to clear existing table and import new data from excel file

[jakobal]

Hi,

I'm trying to create a command button which clears data in an existing table and then lets me choose an excel file to import new data. The first row on the excel-file I import consists of the headings.

Is this possible to do in Access?

Any help is very appreciated!

Thanks!

Jakob

 

[vbaInet]

1. Run a Delete query.
2. Run the TransferSpreadsheet method.

Can you give us a short background?

 

[jakobal]

Forgot to say that I am quite new with coding..

I have a form in access where users can choose different options in drop-downs and then create different reports by pushing different buttons.

Two times a week, I extract data from another program. The data is extracted as an excel-file and saved with the date of the extraction. At the moment I import the data by using the wizard in Access.

The extracted data provides a details for that particular day, and therefore it it would be convenient if it was possible to easily select between different files (dates) to update the table I have in Access.

 

[Rkay]

Hi Jakobal,
Are the excel files in the same format every week?

 

[jakobal]

Hi Rkay,

Yes they are. There is no difference in structure or format between the files from different weeks, only the number of records varies.

Jakob

 

[vbaInet]

First of all create a Delete query to delete all the records in the table. Then, use the Import & Export wizard to import the chosen file into the table.

If you want to automate this process, you will need to understand how to code. With regards selecting a file, you can use ghudson's example:

http://www.access-programmers.co.uk/forums/showthread.php?t=97787

 

[jakobal]

Hi vbaInet,

I don't get ghudson's example to work. I keep getting this message:

"2391 - Field 'F1' doesn't exist in destination table 'tImport'"

I've changed the table tImport so that it now has the structure of my excel files, but I keep getting the same message.

Any clue on why?

Thanks!

 

[vbaInet]

No idea. I don't know what ghudson wrote.

Like I said, get the processes working in two steps first before trying out ghudson's File Dialog.

 

[Rkay]

Hi Jakobal,

i think first you need to create a table with the exact same columns as the excel file
On a form create a text box (say text1), a browse button, and an import button.

Then try the code behind the Import button

Private Sub ButtonName_Click()
Dim db as database
Dim strFileName as string
Dim dFileDate as date

Set db = currentdb()
text1.setFocus
strFileName = text1.text

db.execute("Delete * FROM TableName")

'Then parse the strfilename to get the date
dFileDate = Mid(strfilename,X,Y)

'Import file into DB
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "New table name", strFileName, True

'Put the filename and the date into a temporary storage table (need to create a table and replace the names
db.Execute("Insert Into RecordKeepingTable (FileNam,FileDate) Values('" & strfilename & "', " & dFileDate &")")

End sub


And then you can go from there if you want.
If you want to get funky and delete the first two rows from the exel before importing it then we can put it in there, other wise it has to be done manually (Do not delete the header only empty rows above the header)

Let me know how you go