VBA replace Access table everyday automatically

[rplohocky]

I'm not sure if I should be asking this question in this forum or Excel but here goes. I am sent a report of system errors everyday by an excel spreadsheet. We have to clear each error form this report which can be in the thousands. In order to filter and organize these errors, I built a database where I upload this spreadsheet into everyday with new errors. I would love to have a better method to send this daily spreadsheet up into the access database by using VBA.

I have found a couple other posts that appear to do what I'm looking for but they are really old and using old versions of Access. I am using Office 2016, the most current thread I can find shows 2013 Access.

I would really appreciate any ideas or suggestions? I am fairly good at excel VBA and ok at Access VBA. I usually figure out what is happening in VBA but I need something to start from.

 

[xenou]

Hi,
There haven't been any changes to speak in Access for quite a while that would affect this process. I think you could use the posts that you found for MsAccess 97 and up.

 

[rplohocky]

xenou,
Thanks for your response.

I found a youtube video that will work for me. I watched it and did EXACTLY like it shows but I'm getting a "user not defined" error when I click the command button. The video has you create a blank form and install a unbound text box and a command button. Hit the command button and a window pops up to browse for a excel WB. The macro is program to select a specific range and update a table which you already have in access. Here's the code I got from watching the video.

This goes into a module.
<code>
Option Compare Database
Option Explicit
Public status As String
Public Sub importExcelSpreadsheet(filenName As String, tableName As String)
On Error GoTo BadFormat
DoCmd.TransferSpreadsheet aclmport, acSpreadsheetTypeExcel12, tableName, fileName, True

Exit Sub

BadFormat
MsgBox "not import"
End Sub
</code>

This goes into the ******* event for the commandbutton.

<code>
Option Compare Database
Dim diag As Office.FileDialog
Dim item As Variant
Private Sub update_Click()

Set diag = Application.FileDialog(msoFileDialogFilePicker)
diag.AllowMultiSelect = False
diag.Title = "Please select an Excel"
diag.Filters.Clear
diag.Filters.Add "Excel Spreadsheet", "*.xls,*.xlsx"

If diag.Show Then
If MsgBox("confirm click yes", vbYesNo) = vbYes Then
CurrentDb.Execute "DELETE* FROM tbl_Daily_Fuel_Errors"
For Each item In diag.SelectedItems
Me.txt_Show = item
Call DoCmd.TransferSpreadsheet(acImport, _
acSpreadsheetTypeExcel12, "tbl_Daily_Fuel_Errors", item, _
True, "A1:Q1000")

MsgBox "Update Finish"
Next

End If
End If

End Sub
</code>

Any idea why this doesn't work for me when its absolutely the same as his?

 

[rplohocky]

xenou,
BTW the youtube address is Access VBA Import file Excel and Update access Table - YouTube if your interested.

 

[james_lankford]

read this

there are two solutions here

both work

 

[stumac]

The error you are getting is because you do not have a reference set to the Microsoft Office object library. Try setting the reference by going to Tools, References, scroll down the list and check Microsoft Office 16.0 Object Library.

 

[xenou]

I recommend comment out or delete the line On Error GoTo BadFormat. Then you can find out on which line the error is. It might be that the file picker you are using is an office Excel file picker, which would mean that as stumac said you need a reference to the excel library. You probably don't need the file picker actually - do this without the file picker if you know what file you want to import and its the same every day (why make yourself click for it?).

 

[rplohocky]

I recommend comment out or delete the line On Error GoTo BadFormat. Then you can find out on which line the error is. It might be that the file picker you are using is an office Excel file picker, which would mean that as stumac said you need a reference to the excel library. You probably don't need the file picker actually - do this without the file picker if you know what file you want to import and its the same every day (why make yourself click for it?).

Hello xenou,
The issue lied in not having the MS Office 16.0 Object Library checked as stumac suggested. Once that was checked the macro worked as it should.

You mentioned removing filepicker. Can you send me the code that I would use to assign a specific workbook to the macro? Thank you for your help and everyone else!

 

[xenou]

You just take out what you don't need (which is most of it) and use the hardcoded file address:

Private Sub update_Click()

CurrentDb.Execute "DELETE* FROM tbl_Daily_Fuel_Errors"
Call DoCmd.TransferSpreadsheet(acImport, acSpreadsheetTypeExcel12, "tbl_Daily_Fuel_Errors", "C:\myFolder\myFile.xlsx", True, "A1:Q1000")

End Sub

That's all.

In fact, you probably can use defaults for the spreadsheet type, and I'm not sure why you are using the range at the end so you can also try:

Private Sub update_Click()

CurrentDb.Execute "DELETE* FROM tbl_Daily_Fuel_Errors"
Call DoCmd.TransferSpreadsheet(acImport, , "tbl_Daily_Fuel_Errors", "C:\myFolder\myFile.xlsx", True)

End Sub

 

[rplohocky]

You just take out what you don't need (which is most of it) and use the hardcoded file address:

Private Sub update_Click()

CurrentDb.Execute "DELETE* FROM tbl_Daily_Fuel_Errors"
Call DoCmd.TransferSpreadsheet(acImport, acSpreadsheetTypeExcel12, "tbl_Daily_Fuel_Errors", "C:\myFolder\myFile.xlsx", True, "A1:Q1000")

End Sub

That's all.

In fact, you probably can use defaults for the spreadsheet type, and I'm not sure why you are using the range at the end so you can also try:

Private Sub update_Click()

CurrentDb.Execute "DELETE* FROM tbl_Daily_Fuel_Errors"
Call DoCmd.TransferSpreadsheet(acImport, , "tbl_Daily_Fuel_Errors", "C:\myFolder\myFile.xlsx", True)

End Sub

I tried to use your second option but I'm getting errors. I copied the the exact address to the file and pasted it into the sample you had but still no go.

Private Sub update_Click()

CurrentDb.Execute "DELETE* FROM tbl_Daily_Fuel_Errors"
Call DoCmd.TransferSpreadsheet(acImport, , "tbl_Daily_Fuel_Errors", "C:\Users\RPlohocky\Desktop\Error Reports\Fuel Errors\Current Fuel Report.xlsx", True)

End Sub

Your right about grabbing a certain range, it would be great if i didn't have to do that.