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

first, the I: drive is still on the company Intra Net like the other
drive. the I drive is mapped to a netwrok drive starting with \\. If
you open a window Explorer (right click Start and select explore) and go
to Tools - Disconnect Network Drive you will see the drive name that the
I: is mapped to.

Your answer is definitely YES! The question is how you want to write
the data to the file. there are too many ways to mention. You didn't
say if the file was Access or Excel that you wanted to store the
information. That increases the number of ways to get data into the
file.


Usually the best way is to store the common network data is into an
access database using Excel VBA to perform the storage. Network
databases usually have large amount of data a multiple people reading
and writing to the file at the same time. Excel is not the tool to
store data if you have large files or simultaneous access to the
database. Lots of people use Excel as a front end application for an
access database. this is the way I recommend you set up your data. I
can help but you need to make the decision.


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147329

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

"joel" wrote:


first, the I: drive is still on the company Intra Net like the other
drive. the I drive is mapped to a netwrok drive starting with \\. If
you open a window Explorer (right click Start and select explore) and go
to Tools - Disconnect Network Drive you will see the drive name that the
I: is mapped to.

Your answer is definitely YES! The question is how you want to write
the data to the file. there are too many ways to mention. You didn't
say if the file was Access or Excel that you wanted to store the
information. That increases the number of ways to get data into the
file.


Usually the best way is to store the common network data is into an
access database using Excel VBA to perform the storage. Network
databases usually have large amount of data a multiple people reading
and writing to the file at the same time. Excel is not the tool to
store data if you have large files or simultaneous access to the
database. Lots of people use Excel as a front end application for an
access database. this is the way I recommend you set up your data. I
can help but you need to make the decision.


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147329

.

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





--
jb


"sam" wrote:

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

The company Intanet and Network dirvers are probably on the same server
and the only dirfferent is the I: is just an alias to the real network
drive which has a name starting wit two slashes
(\\myserver\mname\....).

I have 3 macros below which you can run from Excel VBA just change tghe
file names where the database is located. to run from Excel VBA you
need to add two references from the VBA menu tools - References

1) MicrosoftAccess 11.0 object library (or latest version on you pc)
2) Microsoft ActiveX object 2.8 library (or latest on your PC)

Make sure you click the check box next to each library.


the macro below will create a database on the C: drive. You can change
the location to any place including a network drive "\\Myserver\mydir\".
After running this macro you can open the file and see the columns that
were created.

Public Const Folder = "C:\Temp\"
Public Const FName = "submission.mdb"
Sub MakeDataBase()

Const DB_Text As Long = 10
Const FldLen As Integer = 40


strDB = Folder & FName

If Dir(strDB) < "" Then
MsgBox ("Database Exists - Exit Macro : " & strDB)
Exit Sub
End If

' Create new instance of Microsoft Access.
Set appAccess = CreateObject("Access.Application")
appAccess.Visible = True


' Open database in Microsoft Access window.
appAccess.NewCurrentDatabase strDB
' Get Database object variable.
Set dbs = appAccess.CurrentDb
' Create new table.
Set tdf = dbs.CreateTableDef("Submissions")

' Create Task/ID field in new table.
Set fld = tdf. _
CreateField("Task_ID", DB_Text, FldLen)
' Append Field and TableDef objects.
tdf.Fields.Append fld

' Create Client Name field in new table.
Set fld = tdf. _
CreateField("Client Name", DB_Text, FldLen)
' Append Field and TableDef objects.
tdf.Fields.Append fld

' Create Effective Date field in new table.
Set fld = tdf. _
CreateField("Effective Date", DB_Text, FldLen)
' Append Field and TableDef objects.
tdf.Fields.Append fld

' Create Imp Mgr field in new table.
Set fld = tdf. _
CreateField("Imp Mgr", DB_Text, FldLen)
' Append Field and TableDef objects.
tdf.Fields.Append fld

' Create Due Date field in new table.
Set fld = tdf. _
CreateField("Due Date", DB_Text, FldLen)
' Append Field and TableDef objects.
tdf.Fields.Append fld

' Create Actual Date field in new table.
Set fld = tdf. _
CreateField("Actual Date", DB_Text, FldLen)
' Append Field and TableDef objects.
tdf.Fields.Append fld

' Create Date Difference field in new table.
Set fld = tdf. _
CreateField("Date Difference", DB_Text, FldLen)
' Append Field and TableDef objects.
tdf.Fields.Append fld

dbs.TableDefs.Append tdf

Set appAccess = Nothing


End Sub

------------------------------------------------------------------------
The next macro [uts data into the above database. You would need to
setup a worksheet with data in the correct columns to run this macro.
This macro only writes data but you could modify to read as well.

Sub Submit()
'filename of database is with MakeDatabase macro

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

strDB = Folder & FName

If Dir(strDB) = "" Then
MsgBox ("Database Doesn't Exists, Create Database" & strDB)
MsgBox ("Exiting Macro")
Exit Sub
End If

ConnectStr = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Folder & FName & ";" & _
"Mode=Share Deny None;"

cn.Open (ConnectStr)
With rs
.Open Source:="Submissions", _
ActiveConnection:=cn, _
CursorType:=adOpenDynamic, _
LockType:=adLockOptimistic, _
Options:=adCmdTable

If .EOF < True Then
.MoveLast
End If
End With

With Sheets("Internal Project Plan")

ClientName = .Range("B4")
ImpMgr = .Range("B5")
LaunchDate = .Range("C4")

LastRow = .Range("K" & Rows.Count).End(xlUp).Row
For RowCount = 7 To LastRow

If UCase(.Range("K" & RowCount)) = "X" Then

DueDate = .Range("E" & RowCount)
ActualDate = .Range("F" & RowCount)
DateDif = .Range("M" & RowCount)
Accurate = .Range("L" & RowCount)
Task_ID = .Range("B" & RowCount)

With rs
.AddNew
!Task_ID = Task_ID
![Client Name] = ClientName
![Effective Date] = LaunchDate
![Imp Mgr] = ImpMgr
![Due Date] = DueDate
![Actual Date] = ActualDate
![Date Difference] = DateDif

.Update
End With
End If
Next RowCount

End With

Set appAccess = Nothing
End Sub

----------------------------------------------------------------------
The final macro setups a database query in excel to retrieve the data.
the first two macro could in one workbook for use by an admistator and
the last macro below could be for users who just need to read the data.
Again the filenames have to be changed.



Public Const Folder = "C:\Temp"
Public Const FName = "submission.mdb"
Sub CreateQuery()
'
' Macro4 Macro
' Macro recorded 1/19/2009 by Joel
'
strDB = Folder & "\" & FName
'
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=MS Access Database;" & _
"DBQ=" & strDB & ";" & _
"DefaultDir=" & Folder & ";" & _
"DriverId=25;" & _
"FIL=MS Access;" & _
"MaxBufferSize=2048;" & _
"PageTimeout=5"), _
Array(";")), Destination:=Range("A1"))

.CommandText = Array( _
"SELECT Submissions.Task_ID," & _
"Submissions.`Client Name`," & _
"Submissions.`Effective Date`," & _
"Submissions.`Imp Mgr`," & _
"Submissions.`Due Date`," & _
"Submissions.`Actual Date`," & _
"Submissions.`Date Difference`" & _
Chr(13) & "" & Chr(10) & _
"FROM `C:\temp\submission`.Submissions Submissions")
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147329

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

"john" wrote:

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





--
jb


"sam" wrote:

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