UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Use Access Vba To Populate Excel Userform, Access 2016    
 
   
tobyhanna
post Oct 1 2019, 08:27 AM
Post#1



Posts: 156
Joined: 7-February 06



Hello,

I have an Access database and an Excel macro enabled file. The user wishes to open the Excel file using a command button on an Access data entry form. This works.

After opening the Excel file, a particular macro should run. This works too.

My stumbling block begins here. The Excel macro opens a Userform (in Excel) and the user wishes the fields to be pre-populated with the data from the Access form that opened the Excel file and ran the macro.

How do I reference and set the values of the Userform fields using Access VBA? And where do I do this, before or after opening the Userform? I've been searching and testing but have been in circles. I found how to do this within Excel. Can I do this from Access with automation? I'm writing the Access part. The Excel file is supplied and I'd rather avoid any modifications on the Excel side.

Thanks for any pointers,
Liesl
Go to the top of the page
 
arnelgp
post Oct 1 2019, 10:18 AM
Post#2



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


from within Access, just after opening the workbook,
add to existing sheet (or new one) the data from the form:

eg:
CODE
with xlWB.Sheet1
    .Range("a1")= Me!FirstName
    .Range("b1")=Me!LastName
    …
    etc.
End With


the fetch this data through the UserForm.
This post has been edited by arnelgp: Oct 1 2019, 10:18 AM

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
DanielPineault
post Oct 1 2019, 10:55 AM
Post#3


UtterAccess VIP
Posts: 6,978
Joined: 30-June 11



What about dynamically creating and populating a worksheet with values and then use the UserForm_Initialize() proc to see if the sheet exists, if so then use the values to populate the form controls, if not, do nothing?

Also, since you can programmatically assign UserForm controls values through VBA you could also do it all on the fly

Dim frm As Object
Set frm = UserForm1
frm.Controls("Textbox1") = "Hey" 'Must be done before .Show!
frm.Show
Set frm = Nothing

Here's yet one more approach, see: https://stackoverflow.com/questions/3295435...-from-ms-access

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
tobyhanna
post Oct 1 2019, 01:30 PM
Post#4



Posts: 156
Joined: 7-February 06



Thank you for the replies.

The .Range("a1") refers to a worksheet. I need to refer to a UserForm field, such as one named "Contact" where Contact is a field in a UserForm, not a column in a spreadsheet.

I saw your link, thank you. I looked at it closer this time but it all seems to be within Excel.

I need to keep this in Access VBA, I can not modify the Excel file. Is this possible?

How do I refer to an Excel UserForm from Access VBA?

I added the dim frm as object to my Access VBA and the code won't compile when I set it to the name of the Excel UserForm, it's a data type mismatch.
CODE
        Dim oExcel As Object
        Dim wb As Object
        Dim ws As Object
        Dim frm As Object
        
        On Error Resume Next
        Set oExcel = GetObject(, "Excel.Application")
        If Err.Number <> 0 Then Set oExcel = CreateObject("Excel.Application")
        Err.Clear
        On Error GoTo Proc_Err

        Set wb = oExcel.Workbooks.Open(sFile)    'opens Excel
        Set ws = wb.Sheets("WO DB")        'opens worksheet "WO DB"
        Set frm = "NewWO"            'this produces an error - compile error, type mismatch

        oExcel.Visible = True            'displays Excel
        oExcel.Run "AddJob_Click"        'code behind a button that opens the UserForm "NewWO"
        
What I would like (in Access VBA):
        frm.Controls("Contact") = DLookup("UserName", "tblUser", "User_ID = " & Nz(DLookup("TaskedTo", "tblTasks", "Task_ID = " & iTaskID), 0))
        frm.Controls("DateRaised") = Format(CDate(DateValue(Now())), "d/mm/yy")
        etc...


Thanks for all your help,
Liesl
Go to the top of the page
 
ADezii
post Oct 1 2019, 02:43 PM
Post#5



Posts: 2,690
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. Create an Excel Macro that accepts 2 Arguments. This Macro will:
    1. Show the UserForm.
    2. Populate the Fields on the Userform from the passed Arguments.
  2. From the Access side:
    1. Run the Macro and pass the Arguments:
      CODE
      'Represents a previously created Excel Application Object (appExcel)
      appExcel.Run "mcrDemo", Me![txtFirstName], Me![txtLastName]
  3. Sample Macro Definition:
    CODE
    Sub mcrDemo(varArg1, varArg2)
    UserForm1.Show False

    With UserForm1
      ![txtFirst] = varArg1
      ![txtLast] = varArg2
    End With
    End Sub
  4. Make sure to set the Modal Argument to False as indicated.
  5. Hope this helps.

This post has been edited by ADezii: Oct 1 2019, 02:44 PM
Go to the top of the page
 
tobyhanna
post Oct 1 2019, 04:17 PM
Post#6



Posts: 156
Joined: 7-February 06



Thank you for the example. I have no control over the Excel file or any Excel Macro. I am looking for an Access only solution.

Thanks,
Liesl
Go to the top of the page
 
ADezii
post Oct 3 2019, 08:20 AM
Post#7



Posts: 2,690
Joined: 4-February 07
From: USA, Florida, Delray Beach


QUOTE
I have no control over the Excel file or any Excel Macro. I am looking for an Access only solution.

Given this fact, IMHO, it would be a very difficult task to pro-grammatically populate the UserForm with Access Automation Code.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    22nd November 2019 - 12:37 AM