Send Values from TextBoxes to Specific Sheet on Network?

[ryguy7272]

I am trying to modify the Ron de Bruin code, copy a range from closed
workbook, to actually SEND a range to a closed workbook. Ron’s code is here:
http://www.rondebruin.nl/copy7.htm

The code that I am working with right now is below:
Sub CommandButton1_Click
Application.ScreenUpdating = False
On Error Resume Next

'Call the macro GetRange
Cells(4, 8) = TextBox1.Text
Cells(4, 9) = TextBox2.Text
Cells(5, 8) = TextBox3.Text
Cells(5, 9) = TextBox4.Text
Cells(6, 8) = TextBox5.Text
Cells(6, 9) = TextBox6.Text

GetRange "\\fsrv3\public\Forecast", "Weekly Forecast-Final-week12.xls",
"RVP - Andy", "H4:I6", Sheets("RVP - Andy").Range("A1")

On Error GoTo 0
Application.ScreenUpdating = True
End Sub

Sub GetRange(FilePath As String, FileName As String, SheetName As String, _
SourceRange As String, DestRange As Range)

Dim Start

'Go to the destination range
Application.Goto DestRange

'Resize the DestRange to the same size as the SourceRange
Set DestRange = DestRange.Resize(Range(SourceRange).Rows.Count, _
Range(SourceRange).Columns.Count)

'Add formula links to the closed file
With DestRange
.FormulaArray = "='" & FilePath & "/[" & FileName & "]" & SheetName _
& "'!" & SourceRange

'Wait
Start = Timer
Do While Timer < Start + 2
DoEvents
Loop

'Make values from the formulas
.Copy
.PasteSpecial xlPasteValues
.Cells(1).Select
Application.CutCopyMode = False
End With
End Sub


Clearly this is set up to get a range from a sheet rather than sending a
range to a specific sheet, named ‘RVP – Andy’. How can I send values in 6
TextBoxes on a UserForm to specific cells in a specific sheet of a specific
file on a corporate network? The cell references, sheets, location on the
network will always be the same -- just not sure how to get the data from a
stand alone workbook uploaded to a file on the network.

Any help on this would be greatly appreciated.

Regards,
Ryan---

 

[ryguy7272]

I found a snippet of code on the web that seemed somewhat helpful. here is
my code now:
Sub ssave()
Application.DisplayAlerts = False
Worksheets("RVP - Andy").Range("H4").Copy
vpath = "\\fsrv3\public\Forecast\Weekly Forecast-Final.xls" &
Worksheets("RVP - Andy").Range("H4").Value & ".xls"
ActiveWorkbook.SaveAs (vpath)
Application.DisplayAlerts = True
End Sub

What I am trying to do is simply take the value in Cell H4, from Sheet 'RVP
- Andy', in Workbook 'Weekly Forecast-Final', and transfer it to the same
location on the network drive. The macro above transfers the entire file to
the network. The file is about 6MB, so the transfer take a little while, but
anyway, I don't want to upload the entire file, just a simple value in a
certain cell in a certain sheet in the file that is already there on the
network. Ideally, I would like to run this from a UserForm and get values
from a few TextBoxes to update certain ranges in the file that is already
there on the network.

Although I don't know how to do this, based on various things that I've seen
Excel do in the past, I'm pretty sure this is possible. Any ideas?


Regards,
Ryan---
--
RyGuy

I am trying to modify the Ron de Bruin code, copy a range from closed
workbook, to actually SEND a range to a closed workbook. Ron’s code is here:
http://www.rondebruin.nl/copy7.htm

The code that I am working with right now is below:
Sub CommandButton1_Click
Application.ScreenUpdating = False
On Error Resume Next

'Call the macro GetRange
Cells(4, 8) = TextBox1.Text
Cells(4, 9) = TextBox2.Text
Cells(5, 8) = TextBox3.Text
Cells(5, 9) = TextBox4.Text
Cells(6, 8) = TextBox5.Text
Cells(6, 9) = TextBox6.Text

GetRange "\\fsrv3\public\Forecast", "Weekly Forecast-Final-week12.xls",
"RVP - Andy", "H4:I6", Sheets("RVP - Andy").Range("A1")

On Error GoTo 0
Application.ScreenUpdating = True
End Sub

Sub GetRange(FilePath As String, FileName As String, SheetName As String, _
SourceRange As String, DestRange As Range)

Dim Start

'Go to the destination range
Application.Goto DestRange

'Resize the DestRange to the same size as the SourceRange
Set DestRange = DestRange.Resize(Range(SourceRange).Rows.Count, _
Range(SourceRange).Columns.Count)

'Add formula links to the closed file
With DestRange
.FormulaArray = "='" & FilePath & "/[" & FileName & "]" & SheetName _
& "'!" & SourceRange

'Wait
Start = Timer
Do While Timer < Start + 2
DoEvents
Loop

'Make values from the formulas
.Copy
.PasteSpecial xlPasteValues
.Cells(1).Select
Application.CutCopyMode = False
End With
End Sub


Clearly this is set up to get a range from a sheet rather than sending a
range to a specific sheet, named ‘RVP – Andy’. How can I send values in 6
TextBoxes on a UserForm to specific cells in a specific sheet of a specific
file on a corporate network? The cell references, sheets, location on the
network will always be the same -- just not sure how to get the data from a
stand alone workbook uploaded to a file on the network.

Any help on this would be greatly appreciated.

Regards,
Ryan---

 

[Ron de Bruin]

You can use ADO
http://www.erlandsendata.no/english/index.php?t=envbadac

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

I found a snippet of code on the web that seemed somewhat helpful. here is
my code now:
Sub ssave()
Application.DisplayAlerts = False
Worksheets("RVP - Andy").Range("H4").Copy
vpath = "\\fsrv3\public\Forecast\Weekly Forecast-Final.xls" &
Worksheets("RVP - Andy").Range("H4").Value & ".xls"
ActiveWorkbook.SaveAs (vpath)
Application.DisplayAlerts = True
End Sub

What I am trying to do is simply take the value in Cell H4, from Sheet 'RVP
- Andy', in Workbook 'Weekly Forecast-Final', and transfer it to the same
location on the network drive. The macro above transfers the entire file to
the network. The file is about 6MB, so the transfer take a little while, but
anyway, I don't want to upload the entire file, just a simple value in a
certain cell in a certain sheet in the file that is already there on the
network. Ideally, I would like to run this from a UserForm and get values
from a few TextBoxes to update certain ranges in the file that is already
there on the network.

Although I don't know how to do this, based on various things that I've seen
Excel do in the past, I'm pretty sure this is possible. Any ideas?


Regards,
Ryan---
--
RyGuy

I am trying to modify the Ron de Bruin code, copy a range from closed
workbook, to actually SEND a range to a closed workbook. Ron’s code is here:
http://www.rondebruin.nl/copy7.htm

The code that I am working with right now is below:
Sub CommandButton1_Click
Application.ScreenUpdating = False
On Error Resume Next

'Call the macro GetRange
Cells(4, 8) = TextBox1.Text
Cells(4, 9) = TextBox2.Text
Cells(5, 8) = TextBox3.Text
Cells(5, 9) = TextBox4.Text
Cells(6, 8) = TextBox5.Text
Cells(6, 9) = TextBox6.Text

GetRange "\\fsrv3\public\Forecast", "Weekly Forecast-Final-week12.xls",
"RVP - Andy", "H4:I6", Sheets("RVP - Andy").Range("A1")

On Error GoTo 0
Application.ScreenUpdating = True
End Sub

Sub GetRange(FilePath As String, FileName As String, SheetName As String, _
SourceRange As String, DestRange As Range)

Dim Start

'Go to the destination range
Application.Goto DestRange

'Resize the DestRange to the same size as the SourceRange
Set DestRange = DestRange.Resize(Range(SourceRange).Rows.Count, _
Range(SourceRange).Columns.Count)

'Add formula links to the closed file
With DestRange
.FormulaArray = "='" & FilePath & "/[" & FileName & "]" & SheetName _
& "'!" & SourceRange

'Wait
Start = Timer
Do While Timer < Start + 2
DoEvents
Loop

'Make values from the formulas
.Copy
.PasteSpecial xlPasteValues
.Cells(1).Select
Application.CutCopyMode = False
End With
End Sub


Clearly this is set up to get a range from a sheet rather than sending a
range to a specific sheet, named ‘RVP – Andy’. How can I send values in 6
TextBoxes on a UserForm to specific cells in a specific sheet of a specific
file on a corporate network? The cell references, sheets, location on the
network will always be the same -- just not sure how to get the data from a
stand alone workbook uploaded to a file on the network.

Any help on this would be greatly appreciated.

Regards,
Ryan---