Sending datas into closed workbook.

asyamonique

Well-known Member
Joined
Jan 29, 2008
Messages
1,280
Office Version
  1. 2013
Platform
  1. Windows
Code:
Dim asy As Long
With Sheets("pit")
If .Cells(1, 101).Value = "" Then
asy = 1
Else
asy = .Cells(Rows.Count, 101).End(xlUp).Row + 1
End If
      
.Cells(asy, 101) = ComboBox9.Value
.Cells(asy, 102) = TextBox27.Value
.Cells(asy, 103) = ComboBox8.Value
.Cells(asy, 104) = TextBox19.Value
.Cells(asy, 105) = TextBox28.Value
.Cells(asy, 106) = TextBox18.Value
.Cells(asy, 107) = ComboBox4.Value
.Cells(asy, 108) = TextBox26.Value
.Cells(asy, 109) = TextBox8.Value & "      " & TextBox23.Value
.Cells(asy, 110) = TextBox7.Value & "      " & ComboBox5.Value
End With

Good Day,
The code above sending the datas on related targets in same workbook.Is it possible to make it different way like instead of putting in same workbook it will sent the datas to another closed workbook?
Many Thanks
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
asyamonique,

Sending datas into closed workbook

I do not think that you can send data to a closed workbook.


But, you can get information from a closed workbook.

Maybe one of the following will assist you, or point you in the right direction:

Transferring data from a closed workbook
http://www.vbaexpress.com/forum/showthread.php?t=30881

Author of a Closed Workbook
http://www.mrexcel.com/forum/showthread.php?t=476634

Get data from closed workbooks
http://www.erlandsendata.no/english/index.php?t=envbadac
 
Upvote 0
Why not just open the workbook?

Or have it open while the userform is open?
 
Upvote 0
Actually, I've done some testing and you could use ADO to send data to a closed workbook.

The destintation would have to be in table format and I think, though I'm not sure, a named range.

I think that because I couldn't get it to work with a sheet name, so I was probably doing something wrong there.:)
 
Upvote 0
Hi,

Open the workbook, change it, close it, but keep these operations invisible.
Code:
Sub ChangeClosedWorkbook()
Dim xl As Excel.Application, wb As Workbook
 
Application.ScreenUpdating = False
Set xl = New Excel.Application
Set wb = xl.Workbooks.Open("J:\pathpath\filename.xls")
wb.Sheets("sheet1").Range("A14").Value = ActiveSheet.Range("A1").Value
wb.Close SaveChanges:=True
xl.Quit
Set xl = Nothing
Set wb = Nothing
Application.ScreenUpdating = True
End Sub
kind regards,
Erik
 
Upvote 0
Code:
Sub Button1_Click()
Dim wb As Workbook
Dim LR As Long
Set wb = Workbooks.Open(Filename:="x:\xxxxx\xxxxx.xls")
Dim asy As Long
With Sheets("main")
If .Cells(1, 101).Value = "" Then
asy = 1
Else
asy = .Cells(Rows.Count, 101).End(xlUp).Row + 1
End If
.Cells(asy, 101) = TextBox28.Value
.Cells(asy, 102) = TextBox19.Value
.Cells(asy, 103) = TextBox18.Value
.Cells(asy, 104) = ComboBox4.Value
.Cells(asy, 105) = TextBox7.Value
.Cells(asy, 106) = ComboBox5.Value
.Cells(asy, 107) = TextBox8.Value
.Cells(asy, 108) = TextBox23.Value
.Cells(asy, 109) = TextBox26.Value
End With
wb.Save
wb.Close False
End Sub

Different way which also i had figured out.
Cheers.
 
Upvote 0
Hi,

Open the workbook, change it, close it, but keep these operations invisible.
Code:
Sub ChangeClosedWorkbook()
Dim xl As Excel.Application, wb As Workbook
 
Application.ScreenUpdating = False
Set xl = New Excel.Application
Set wb = xl.Workbooks.Open("J:\pathpath\filename.xls")
wb.Sheets("sheet1").Range("A14").Value = ActiveSheet.Range("A1").Value
wb.Close SaveChanges:=True
xl.Quit
Set xl = Nothing
Set wb = Nothing
Application.ScreenUpdating = True
End Sub
kind regards,
Erik


Thanks Erick,
Another one below also worked well.
Code:
Sub Button1_Click()
Dim wb As Workbook
Dim LR As Long
Set wb = Workbooks.Open(Filename:="x:\xxxxx\xxxxx.xls")
Dim asy As Long
With Sheets("main")
If .Cells(1, 101).Value = "" Then
asy = 1
Else
asy = .Cells(Rows.Count, 101).End(xlUp).Row + 1
End If
.Cells(asy, 101) = TextBox28.Value
.Cells(asy, 102) = TextBox19.Value
.Cells(asy, 103) = TextBox18.Value
.Cells(asy, 104) = ComboBox4.Value
.Cells(asy, 105) = TextBox7.Value
.Cells(asy, 106) = ComboBox5.Value
.Cells(asy, 107) = TextBox8.Value
.Cells(asy, 108) = TextBox23.Value
.Cells(asy, 109) = TextBox26.Value
End With
wb.Save
wb.Close False
End Sub
Cheers
 
Upvote 0
consider adding
Code:
Application.ScreenUpdating = False
 
 
 
Application.ScreenUpdating = True
so you will not see all the "movement" on your screen
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top