PDA

View Full Version : link two workbooks



hussy
04-03-2012, 12:29 AM
Hi all,
In my project, I have three forms, calendar, userform1(which is data entry form) and Userform2(which is filter and copy form)

I want to keep data entry form,filter form separate workbook (let say workbook1) and the all the other things (records) should be in another workbook (let say workbook2). As I learnt last couple of days,

Set rngSource = Worksheets("item").Range("c2:c100") should be changed to
Set rngSource = Workbooks("workbook2").Worksheets("item").Range("c2:c100").

But the problem is, I have to open both file to work with forms. I don't want to open workbook2. As I understand there are no way other than opening workbook2. I think the best way to do it, when the forms open, workbook should open automatically, but workbook should not appears to the user(need to hide from the user). What do you think? Problem is I don't know the code for that. I tried to find. but I couldn't.
Could you please tell me the solution for that.








'to enter data into another workbook


Set rngSource = Worksheets("item").Range("c2:c1048576")
'should be changed to
Set rngSource = Workbooks("bookname").Worksheets("item").Range("c2:c1048576").

Bob Phillips
04-03-2012, 01:02 AM
Set wbCopy = Workbooks.Open Filename:="C:\my workbooks\Workbook2.xls"
Windows(wbCopy.Name).Visible = False

hussy
04-03-2012, 07:23 PM
Set wbCopy = Workbooks.Open Filename:="C:\my workbooks\Workbook2.xls"
Windows(wbCopy.Name).Visible = False


my VB code is
Private Sub UserForm_Initialize()
Set wbCopy = Workbooks.Open("E:\udeshika\excel\New Folder - Copy\test.xlsx")
Windows(wbCopy.Name).Visible = False

End Sub

problem 1
The workbook containing data entry form is book1.xlsm.
when I only open book1.xlsm and try to open the form, an error msg coming out and says "runtime error 9, subscript out of range"
Then I manually open the test.xlsx(even though I open it, I cant see the workbook) and try to open the form, now it works fine.
That means I have to open other workbook every time ?
problem 2
Then I closed all workbooks and try to open my data record work book (test.xlsx) . Now I cant see the sheets of that book.
Could you please solve above two problems for me ..
thanks

Aussiebear
04-04-2012, 04:48 PM
You can try the following:

Open the properties window in the VBA Editor environment and set the the sheets visibility to Visible.

hussy
04-04-2012, 05:12 PM
You can try the following:

Open the properties window in the VBA Editor environment and set the the sheets visibility to Visible.

visible = 1-xlsheetvisible for all sheets in the "test.xmls" . What should I do now?
Still I have to open both workbooks. Is there any way that I dont need to open the Data record workbook(in my case book1.xlsm) manually?

IanFScott
04-05-2012, 05:50 AM
If Workbook2 is just a database then use ADO (set a reference to Microsoft Active Data Objects 2.5 or higher), the routine below collects all the data in a recordset (Workbook2 does NOT open) - do what you like with the data and write it back where edited.


Sub ADOTestXLConnection()
Dim xlCon As New ADODB.Connection
Dim oRset As New ADODB.Recordset
Dim FPath As String
Dim FName As String
Dim RCounter As Integer
Dim FCounter As Integer
Dim WS As Worksheet
Set WS = ThisWorkbook.Sheets(1)
WS.Rows("2:500").Delete
FPath = "N:\Test\"
RCounter = 2
FName = "Workbook2.xlsx"
With xlCon
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & FPath & FName & ";" & _
"Extended Properties=""Excel 8.0;HDR=No;"""
.Open
End With
xlRSet.Open "SELECT * FROM [Sheet1$A1:J108]", xlCon, adOpenKeyset, adLockOptimistic
Do
For FCounter = 1 To 10
WS.Cells(RCounter, FCounter).Value = xlRSet.Fields(FCounter - 1).Value
Next
RCounter = RCounter + 1
xlRSet.MoveNext
Loop Until xlRSet.EOF
xlRSet.Close
xlCon.Close
Set xlRSet = Nothing
Set xlCon = Nothing
End Sub