Sending datas into closed workbook.

asyamonique

Well-known Member
Joined
Jan 29, 2008
Messages
1,093
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
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,648
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,392
Office Version
365
Platform
Windows
Why not just open the workbook?

Or have it open while the userform is open?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,392
Office Version
365
Platform
Windows
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
 

asyamonique

Well-known Member
Joined
Jan 29, 2008
Messages
1,093
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.
 

asyamonique

Well-known Member
Joined
Jan 29, 2008
Messages
1,093
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
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
consider adding
Code:
Application.ScreenUpdating = False
 
 
 
Application.ScreenUpdating = True
so you will not see all the "movement" on your screen
 

Forum statistics

Threads
1,084,741
Messages
5,379,534
Members
401,612
Latest member
brak_man

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top