Make excel Worksheet as data base

[youu917]

Is it possible to make a worksheet auto tracking the data from other
worksheet in the server? a little bit like making an excel
database,however, i thought that excel data sheet cannot enable multi-
access. Is it possible to overcome this problem?

 

[Arvi Laanemets]

Hi

It's possible to several users open same Excel workbook simultaneously, but
only the first user has write rihgt. When he/she closes the file, the next
user gets write right, etc.

To overcome this you can give every user his/her own workbook. You have to
design those workbooks in such a way, that any kind of information may be
entered or modified only by certain user. The information entered by other
users may be only displayed. To display the information from other
workbooks, you use link formulas (like ='C:\Documents and
Settings\YourProfile\My Documents\[YourFile.xls]SheetName'!A1), or you
import needed data from other workbook(s), using ODBC querie(s), to special
(hidden) sheets and refer to these query tables to get data into working
sheets.

Both ways don't interfere with user working with his/her data. But either of
the ways doesn't reflect changes done by other users immediately. I myself
design such file systems so, that data are refreshed when the file is
opened.

 

[youu917]

Hi

It's possible to several users open same Excel workbook simultaneously, but
only the first user has write rihgt. When he/she closes the file, the next
user gets write right, etc.

To overcome this you can give every user his/her own workbook. You have to
design those workbooks in such a way, that any kind of information may be
entered or modified only by certain user. The information entered by other
users may be only displayed. To display the information from other
workbooks, you use link formulas (like ='C:\Documents and
Settings\YourProfile\My Documents\[YourFile.xls]SheetName'!A1), or you
import needed data from other workbook(s), using ODBC querie(s), to special
(hidden) sheets and refer to these query tables to get data into working
sheets.

Both ways don't interfere with user working with his/her data. But either of
the ways doesn't reflect changes done by other users immediately. I myself
design such file systems so, that data are refreshed when the file is
opened.

--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )

Is it possible to make a worksheet auto tracking the data from other
worksheet in the server? a little bit like making an excel
database,however, i thought that excel data sheet cannot enable multi-
access. Is it possible to overcome this problem?- Òþ²Ø±»ÒýÓÃÎÄ×Ö -

- ÏÔʾÒýÓõÄÎÄ×Ö -

Where can i find those materials taught about ODBC query apply to
excel sheet? I'd like to try the way import needed data using ODBC
queries.Thank you

 

[Abhishake]

hope this can help you

Set aCon = New ADODB.Connection
With aCon
..Provider = "Microsoft.Jet.OLEDB.4.0"
..Properties("Extended Properties") = "Excel 8.0"
..Open wrkbk.Path & "\" & wrkbk.Name
End With

rsMaterial.Open "Select Distinct ShipTo,Brand From [" & ShName1 & "$" &
strRng & "] Group by ShipTo ,Brand ", aCon, adOpenDynamic
If Not (rsMaterial.EOF And rsMaterial.BOF) Then
Set TmpSh = ThisWorkbook.Sheets("Temp")
Set WIFSh = ThisWorkbook.Sheets("A")

Do While Not rsMaterial.EOF
Set rsRawData = New ADODB.Recordset
Debug.Print rsMaterial(ShipTo) & ":" & rsMaterial(Brand)
rsRawData.Open "Select Week,FcstValue From [" & ShName1 & "$" & strRng & "]
Where ShipTo = " & rsMaterial(ShipTo) & " And Brand = " & rsMaterial(Brand) &
" Order By Week1 ", aCon

''''' In above statement it gives the error after some time

If Not (rsRawData.EOF And rsRawData.BOF) Then
TmpSh.Cells.Clear
TmpSh.Cells(1, 1).CopyFromRecordset rsRawData
col = WIFSh.Range("1:1").Find(what:=TmpSh.Cells(1, 1),
lookat:=xlWhole).Column
TmpSh.Range("B1:B" & TmpSh.Cells(1, 2).End(xlDown).Row).Copy
WIFSh.Cells(WIFSh.Cells(65536, 2).End(xlUp).Row + 1, col).PasteSpecial
Paste:=xlAll, Transpose:=True
WIFSh.Cells(WIFSh.Cells(65536, col).End(xlUp).Row, 1) = ""
WIFSh.Cells(WIFSh.Cells(65536, col).End(xlUp).Row, 2) = rsMaterial(ShipTo)
WIFSh.Cells(WIFSh.Cells(65536, col).End(xlUp).Row, 3) = rsMaterial(Brand)
WIFSh.Cells(WIFSh.Cells(65536, col).End(xlUp).Row, 4) = "SU"
End If
rsRawData.Close
Set rsRawData = Nothing
rsMaterial.MoveNext
Loop
End If

Hi

It's possible to several users open same Excel workbook simultaneously, but
only the first user has write rihgt. When he/she closes the file, the next
user gets write right, etc.

To overcome this you can give every user his/her own workbook. You have to
design those workbooks in such a way, that any kind of information may be
entered or modified only by certain user. The information entered by other
users may be only displayed. To display the information from other
workbooks, you use link formulas (like ='C:\Documents and
Settings\YourProfile\My Documents\[YourFile.xls]SheetName'!A1), or you
import needed data from other workbook(s), using ODBC querie(s), to special
(hidden) sheets and refer to these query tables to get data into working
sheets.

Both ways don't interfere with user working with his/her data. But either of
the ways doesn't reflect changes done by other users immediately. I myself
design such file systems so, that data are refreshed when the file is
opened.

--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )

Is it possible to make a worksheet auto tracking the data from other
worksheet in the server? a little bit like making an excel
database,however, i thought that excel data sheet cannot enable multi-
access. Is it possible to overcome this problem?- éšè—被引用文字 -

- 显示引用的文字 -

Where can i find those materials taught about ODBC query apply to
excel sheet? I'd like to try the way import needed data using ODBC
queries.Thank you

 

[Arvi Laanemets]

Hi

At start, you have to define the source table as named range - and you can't
use dynamic ranges for it. This named range MUST include header row at top
(a single row!), and it may have any number of empty rows at bottom. Headers
are used as field names in your query later.

Also, you have to activate Analysis Toolpack Add-In - otherwise you get
later problems whenever the source workbook is closed. I don't know why, and
how Analysis Toolpack affects ODBC, but this is how it works, at least in MS
Office 2000.

Now close the source workbook, or save it.

With target sheet of target workbook activated, select from menu Data>Get
External Data>New Database Query. An Choose Data Source window opens. Select
'Excel Files*' ad data source. OK.

An Select Workbook window opens. Select the target workbook. OK

The Query Wizard opens. Select previously created name as table, and proceed
with wizard (Remember that you had some amount of empty rows at bottom of
source range, which you don't want returned - so one of filter conditions
for some key column must be, that it 'Is Not Null'.

When you are finishing, you are given the opportunity to determine the
leftmost upper cell for returned query table - by default it is the cell you
selected initially when you started with query wizard. Also you can
determine a lot of different properties for your query there, like how and
when the query is refreshed, are field heardes returned too or not, what
happens with old data when the query is refreshed, are formulas adjacent to
returned query table filled down or not (when you check this option, then
you probably have to select "Insert entire rows for new data, clear unused
cells" too, uncheck "Include field names", and later add your own header row
at top of table, where in columns with formulas, the header is created with
same formula, like =IF($A1="FirstHeader",""FirstFormula",$B1+$C1) -
otherwise you lost all your formulas whenever the query returns no rows.)


--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )

Hi

It's possible to several users open same Excel workbook simultaneously,
but
only the first user has write rihgt. When he/she closes the file, the next
user gets write right, etc.

To overcome this you can give every user his/her own workbook. You have to
design those workbooks in such a way, that any kind of information may be
entered or modified only by certain user. The information entered by other
users may be only displayed. To display the information from other
workbooks, you use link formulas (like ='C:\Documents and
Settings\YourProfile\My Documents\[YourFile.xls]SheetName'!A1), or you
import needed data from other workbook(s), using ODBC querie(s), to
special
(hidden) sheets and refer to these query tables to get data into working
sheets.

Both ways don't interfere with user working with his/her data. But either
of
the ways doesn't reflect changes done by other users immediately. I myself
design such file systems so, that data are refreshed when the file is
opened.

--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )

Is it possible to make a worksheet auto tracking the data from other
worksheet in the server? a little bit like making an excel
database,however, i thought that excel data sheet cannot enable multi-
access. Is it possible to overcome this problem?- Òþ²Ø±»ÒýÓÃÎÄ×Ö -

- ÏÔʾÒýÓõÄÎÄ×Ö -

Where can i find those materials taught about ODBC query apply to
excel sheet? I'd like to try the way import needed data using ODBC
queries.Thank you

 

[RB Smissaert]

Have a look at posting number 7 in this thread:
http://groups.google.co.uk/group/mi...*+author:RB+author:Smissaert#f97bc1fa333007ec

RBS

Hi

It's possible to several users open same Excel workbook simultaneously,
but
only the first user has write rihgt. When he/she closes the file, the next
user gets write right, etc.

To overcome this you can give every user his/her own workbook. You have to
design those workbooks in such a way, that any kind of information may be
entered or modified only by certain user. The information entered by other
users may be only displayed. To display the information from other
workbooks, you use link formulas (like ='C:\Documents and
Settings\YourProfile\My Documents\[YourFile.xls]SheetName'!A1), or you
import needed data from other workbook(s), using ODBC querie(s), to
special
(hidden) sheets and refer to these query tables to get data into working
sheets.

Both ways don't interfere with user working with his/her data. But either
of
the ways doesn't reflect changes done by other users immediately. I myself
design such file systems so, that data are refreshed when the file is
opened.

--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )

Is it possible to make a worksheet auto tracking the data from other
worksheet in the server? a little bit like making an excel
database,however, i thought that excel data sheet cannot enable multi-
access. Is it possible to overcome this problem?- Òþ²Ø±»ÒýÓÃÎÄ×Ö -

- ÏÔʾÒýÓõÄÎÄ×Ö -

Where can i find those materials taught about ODBC query apply to
excel sheet? I'd like to try the way import needed data using ODBC
queries.Thank you

 

[youu917]

Have a look at posting number 7 in this thread:http://groups.google.co.uk/group/microsoft.public.excel.programming/b...

RBS

Hi

It's possible to several users open same Excel workbook simultaneously,
but
only the first user has write rihgt. When he/she closes the file, the next
user gets write right, etc.

To overcome this you can give every user his/her own workbook. You have to
design those workbooks in such a way, that any kind of information may be
entered or modified only by certain user. The information entered by other
users may be only displayed. To display the information from other
workbooks, you use link formulas (like ='C:\Documents and
Settings\YourProfile\My Documents\[YourFile.xls]SheetName'!A1), or you
import needed data from other workbook(s), using ODBC querie(s), to
special
(hidden) sheets and refer to these query tables to get data into working
sheets.

Both ways don't interfere with user working with his/her data. But either
of
the ways doesn't reflect changes done by other users immediately. I myself
design such file systems so, that data are refreshed when the file is
opened.

- ÏÔʾÒýÓõÄÎÄ×Ö -

Where can i find those materials taught about ODBC query apply to
excel sheet? I'd like to try the way import needed data using ODBC
queries.Thank you- Òþ²Ø±»ÒýÓÃÎÄ×Ö -

- ÏÔʾÒýÓõÄÎÄ×Ö -

Thank you Arvi, i'll try to do it and will ask you again if any
problem occured.
Thank you very much.