Connection With Excel Data base Workbook by VBA

krishnaoptif · Oct 3, 2011

Hi,

I made a Userform in excel 2007... i want --when user will fill this form and will click on save button then data should go in other workbook which is placed in share drive...assume UserForm workbook is Userfrom_Workbook which is place on user's desktop -----and Database Workbook is DB_workbook which is placed in share drive..Please write the example code for the same..

Please help me this would be very helpful for my process.

bertie · Oct 3, 2011

Here is some sample code to output the contents of two text boxes to another workbook, sheet1, columns A and B.

I have highlighted where you will need to edit.
This code would be placed in the UserForm module.

Code:

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CommandButton1_Click()
  [COLOR=green]'this is the save button[/COLOR]
  [COLOR=darkblue]Dim[/COLOR] wb [COLOR=darkblue]As[/COLOR] Workbook
  [COLOR=darkblue]Dim[/COLOR] lr [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]    [COLOR=green]'last row in database workbook[/COLOR]
  
  [COLOR=green]'open the workbook[/COLOR]
  [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
    [COLOR=green]'********* EDIT PATH ************[/COLOR]
    [COLOR=darkblue]Set[/COLOR] wb = Workbooks.Open([COLOR=Red]"C:\Temp\Shared\DB_Workbook.xls"[/COLOR])
    [COLOR=darkblue]If[/COLOR] wb [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
      MsgBox "File not found!"
      [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
  [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0
  
  [COLOR=green]'output[/COLOR]
  '******** EDIT SHEET NAME ************
  [COLOR=darkblue]With[/COLOR] wb.Sheets([COLOR=Red]"Sheet1"[/COLOR])
    lr = .Range("[COLOR=Red]A[/COLOR]" & .Rows.Count).End(xlUp).Row + 1
    .Range("[COLOR=Red]A[/COLOR]" & lr).Value = TextBox1.Value
    .Range("[COLOR=Red]B[/COLOR]" & lr).Value = TextBox2.Value
  [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
  
  [COLOR=green]'close the workbook[/COLOR]
  wb.Close SaveChanges:=[COLOR=darkblue]True[/COLOR]
  [COLOR=darkblue]Set[/COLOR] wb = [COLOR=darkblue]Nothing[/COLOR]
  
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

krishnaoptif · Oct 3, 2011

Hi bertie,

Can we save data without open the DB_workbook.xls ??????..

if can then please write the code..

if not then please tell me simultaneously 15 Users can save data in DB_workbook.xls because there is a doubt in my mind...when User1 is saving data then file will open and the same time User2 Saving data then file will go for Read only ...so please tell me data can save simultaneously or not ???

Regards,
Krishna

bertie · Oct 3, 2011

Can we save data without open the DB_workbook?

The short answer is yes.

But!
The connection string to access the file differs for different versions of MS-Office. For example, I use Excel 2003, I would connect via the ADO JET engine. Whist Excel 2007 would use the ACE.OLEDB engine. If your users operate various versions of Excel the coding would get messy.

And!
This will not solve your problem of multiple users accessing the file simultaniously. For this functionality you should consider storing your database in Ms-Access.

Then you will need to consider the connection string problem outlined above.

Connection With Excel Data base Workbook by VBA

krishnaoptif

Board Regular

Excel Facts

bertie

Well-known Member

krishnaoptif

Board Regular

bertie

Well-known Member

Similar threads

Forum statistics

Share this page

Connection With Excel Data base Workbook by VBA

krishnaoptif

Board Regular

Excel Facts

bertie

Well-known Member

krishnaoptif

Board Regular

bertie

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock