Improve company productivity with a Business Account.Sign Up

x
?
Solved

Access To Excel Form

Posted on 2014-04-20
12
Medium Priority
?
540 Views
Last Modified: 2014-04-22
Hello All,

I think this may be a tough nut to crack but ....i want to open an excel file via access VBA. Now the access vba should be able to access userform2 of that excel workbook and fill one of the textbox.

Is that possible?
VBC or something?

Thank you
0
Comment
Question by:Rayne
  • 7
  • 5
12 Comments
 
LVL 37

Expert Comment

by:Norie
ID: 40011877
I think it might be possible but I have to ask why you want/need to access the Excel userform to fill in a textbox?

What does filling in the textbox do?

Also, in the Access VBA you currently have are you automating Excel?
0
 

Author Comment

by:Rayne
ID: 40011883
ok, so there is a excel workbook that is pass protected.so when users open the workbook, the workbook shows up a userform that requires a pass value. once the user enter in the pass value and hit ok, the workbook filters the data as per the login privilege and then show up the info in sheet1.

That's the excel piece.

For the access  - i have all the data tables stores in he access. And have a custom "refresh" vba button. When  press it, it basically copies the records set from the access table and then  puts in sheet2 on workbook.

now the problem - when the (access) VBA refresh happens, the userform shows up (as it is in the workbook on open event on the worokbook)

so how do i do the access refresh from access vba and not have to worry about facing this pass form...
0
 
LVL 37

Expert Comment

by:Norie
ID: 40011885
How are you opening the Excel workbook?
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 

Author Comment

by:Rayne
ID: 40011890
Thank you,

Like this

    Set objXL = New excel.Application
   ' objXL.Visible = True
 
   
    Set wkb = objXL.Workbooks.Open("\fg\wk.xlsm")
0
 
LVL 37

Accepted Solution

by:
Norie earned 2000 total points
ID: 40011899
If you want to bypass the userform when the workbook opens you could disable events in Excel.
objXL.EnableEvents = False

Open in new window

However I'm not sure that's what you want to do.
0
 

Author Comment

by:Rayne
ID: 40011954
Hello Imnorie,

that works like GOLD,

thank you thank you :)
0
 

Author Comment

by:Rayne
ID: 40011957
Imnorie,

once i do this
objXL.EnableEvents = False

all work....BTW - i am calling a workbook macro from access vba too, at the end. That will work even in this setting?


Thank you
0
 
LVL 37

Expert Comment

by:Norie
ID: 40013565
Disabling events should only affect code that's triggered by an event, for example the Workbook_Open event.

There should be no problem with any other code.

How are you calling the 'workbook' macro?
0
 

Author Comment

by:Rayne
ID: 40013599
thank imnorie,

I am doing this
objXL.run myMacro
0
 
LVL 37

Expert Comment

by:Norie
ID: 40013628
Is 'myMacro' the name of the macro?
0
 

Author Comment

by:Rayne
ID: 40013920
yes imnorie
0
 

Author Closing Comment

by:Rayne
ID: 40014682
Hello Imnorie,

i apologize, i totally forgot you gave me the solution :( already
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This tutorial summarizes the causes behind"an unknown error prevented access to the PST File”.  It also suggests the various solutions to fix the problem.
You can use the network upload option and the Office 365 Import service to bulk-import PST files to user mailboxes. Network upload means that you upload the PST files a temporary storage area in the Microsoft cloud.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

608 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question