Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send Values from TextBoxes to Specific Sheet on Network?
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 "ryguy7272" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send Values from TextBoxes to Specific Sheet on Network?
You can use ADO
http://www.erlandsendata.no/english/...php?t=envbadac -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "ryguy7272" wrote in message ... 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 "ryguy7272" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Send and Receive Data From Web Site Into UserForm TextBoxes | Excel Discussion (Misc queries) | |||
How to send variables to textboxes in a web site | Excel Programming | |||
Send data from userform to specific cell on specific sheet? | Excel Programming | |||
How do I send a spreadsheet to a printer outside the network? | Excel Discussion (Misc queries) | |||
macro to send excel sheet to specific email address | Excel Programming |