Access Excel advanced macro questions

[ketangarg86]

I have a excel file which has a button to upload all the data from the Excel file to Access database table. This is working fine. Now below are my new requirements. Could you please help.

1. The push of the excel button should open the access file (located in the same folder) and the associated table. The table opened should only filter and show the files that were imported this time in edit view.

2. Is there a way I can make sure that the user does not click the excel button twice and the data gets duplicated in the access database table.

3. I have the excel template which the user will fill the data in and upload to access database table. Is there a way I can add a button to the excel template to reset the excel template to its original values before user started inputting his/her values.

Thanks....

 

[ranman256]

You really should do this the other way, open access and import the excel table.

When the user pushes the button , move the cursor to another object, say a cell then disable the button. (you cant disable an object that has the focus)

   btnImport.enabled = false

You cant open access tables in excel (you can import them)

The template should already have default values. Once they fill out the sheet, it shouldnt be a template anymore but rather a workbook.
But you can have a button that runs a macro, that resets the existing worksheet cell values

 

[ketangarg86]

Hi,

Thanks but I need to open the access table from excel button. In addition, I need the access table to only show the data which filters from the excel cells.

Would really appreciate your help.

Thanks.

 

[alansidman]

crossposted: Excel - Access questions

Please read this: Excelguru Help Site - A message to forum cross posters

 

[xenou]

You can use an Autoexec macro to have Access open to a certain table. Though personally I would not use a table - use a form in datasheet view so that you actually open a form that looks like a table. This provides you with much better control and more options for user interactions. You can also set Access so that a form will open automatically when Access is opened - so that would work as well.

This is a very awkward way of doing things though. I'd push back and tell whoever this is that it's not a good design. Of course that's not always possible. But the user really shouldn't need to work in both Access and Excel simultaneously (even if the programs interact in the background).

You can prevent double-clicks by putting a little timer flag in the button:

Untested:

Static LastButtonPush As Date
If Now - LastButtonPush < TimeValue("00:00:05") AND LastButtonPush <> 0 Then
    Exit Sub '//Too soon
Else
    LastButtonPush = Now
End If

It is very hard to prevent someone from not pushing data to a table twice if the database itself allows it. At very least, use a timestamp so that you can trace back to when the data was entered to find mistakes. Ideally there will be some way to ensure duplicates are avoided using data definition rules.