View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
ryguy7272 ryguy7272 is offline
external usenet poster
 
Posts: 2,836
Default Send Values from TextBoxes to Specific Sheet on Network?

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. Rons code is he
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---




--
RyGuy