HELP: Populate form fields (intranet) from a workbook(network driv

[sam]

Hi All,

I have a userform that is posted on companys local intranet. Now On this
userform I have certain textfields being populated based on what you input in
another Textfield and the data is residing within the same workbook as the
form is.

For eg: I have a button to launch the userform on Sheet1 and Sheet2 has the
data where certain fields are pulling the data from, such as..
If I input "E112454" in User Id: textbox then It will populate the Name,
Division, Contact information automatically. All this information is in
Sheet2.

BUT.. Now I dont want to keep the data in the same Workbook as the form..
and want to keep it on a seperate workbook which will be on companys shared
drive lets say " I:\" and NOT on the intranet website.. The Form will still
be on the intranet website, its just that now the data will be pulled from a
seperate workbook which is on companys network drive.

Is there a way to do this?

Hope I made it clear

Thanks in advance

 

[sam]

Hi Joel, Thanks for the help..

My main concern is pulling the data from somewhere for excel userform.. The
userform is not on any network drive.. Its posted on the companys local
intranet site.

So basically I want certain fields on the form to populate from a data
source that will be stored on companys network drive.

So I launch the userfrom from the intranet then I input my employee ID and I
want my details to populate automatically from a data source stored on the
netword drive.

If you suggest using access for that kind of data pull, can you help me
understand how to do it?

I was thinking of keeping an excel sheet on the network drive as a data
source and pulling the information from there.

I am open for any suggestions that would help me design the system and
process better.

Thanks a lot

 

[john]

Sam,
Your data is stored in a workbook on network shared drive and you just need
to “extract†data from it to populate your form?
If correct, you could as a suggestion, just make a copy of the shared data
and populate the existing worksheet your form currently extracts the data
from.


Code would look something like following - not tested but hopefully, may do
what you want. Change sheet names and directory etc data as required.

Sub GetSamsData()
Dim DBFile As String
Dim MyPassword As String
Dim DestWB As Workbook
Dim DestRng As Range
Dim SourceWB As Workbook
Dim SourceRng As Range

MyPassword = "sam"

DBFile = "I:\samsfiles\sams.xlsm"


If Dir(DBFile) <> Empty Then

Application.ScreenUpdating = False

Set DestWB = ThisWorkbook

Set SourceWB = Workbooks.Open(DBFile, ReadOnly:=True,
Password:=MyPassword)

Set SourceRng = SourceWB.Worksheets("Sheet1").UsedRange


With DestWB.Sheets("Sheet1")

.Cells.ClearContents

Set DestRng = .Range("A1")

End With

SourceRng.Copy

DestRng.PasteSpecial xlPasteValues, , False, False

SourceWB.Close False

With Application
.CutCopyMode = False
.ScreenUpdating = True
End With

Else

msg = MsgBox(DBFile & Chr(10) & "File Not Found", 16, "Error")

End If

End Sub

call it from forms Initialize event like this:

Private Sub UserForm_Initialize()

GetSamsData

End Sub

 

[sam]

Hey John,

Thanks for the help. I got that to work!

Now they want me to put the data into access database and make the userform
pull data from there.. Is it possible? and how ?

And yea you are right.. the userform is posted on intranet and the access
database will be on a network drive.

Thanks in advance