Sending datas into closed workbook.

[asyamonique]

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

 

[hiker95]

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

 

[Norie]

Why not just open the workbook?

Or have it open while the userform is open?

 

[Norie]

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.

 

[erik.van.geit]

Hi,

Open the workbook, change it, close it, but keep these operations invisible.

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

 

[asyamonique]

Thanks Erick.

 

[asyamonique]

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.

 

[asyamonique]

Hi,

Open the workbook, change it, close it, but keep these operations invisible.

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.

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

 

[erik.van.geit]

consider adding

Application.ScreenUpdating = False
 
 
 
Application.ScreenUpdating = True

so you will not see all the "movement" on your screen