Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send Data From Active Sheet to Closed Sheet on Network
I am trying to find a way to send a range (H4:J6) of data from one
worksheet, which is saved on my desktop, but could be stored anywhere, to a specific sheet on my firm's network (intranet). Basically, I am trying to write to a closed workbook on a network drive. I read this resource: http://www.erlandsendata.no/english/...=envbadacrs2ws I guess it can be done, but I not sure how to actually implement the procedure. I would sincerely appreciate any help that anyone could offer. The network UNC is: \\fsrv3\public\Forecast Regards, Ryan--- -- RyGuy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send Data From Active Sheet to Closed Sheet on Network
I just found this resource:
http://www.j-walk.com/ss/excel/tips/tip82.htm This seems to do pretty much what I want to do, but how can I get it to UPLOAD the data to a file saved on the network? The code that I am working with is below: Private Function GetValue(path, file, sheet, ref) ' Retrieves a value from a closed workbook Dim arg As String ' Make sure the file exists If Right(path, 1) < "\" Then path = path & "\" If Dir(path & file) = "" Then GetValue = "File Not Found" Exit Function End If ' Create the argument arg = "'" & path & "[" & file & "]" & sheet & "'!" & _ Range(ref).Range("A1").Address(, , xlR1C1) ' Execute an XLM macro GetValue = ExecuteExcel4Macro(arg) End Function Sub TestGetValue() p = "\\fsrv3\public\Forecast\Display Forecast" f = "Destination.xls" s = "Sheet1" a = "A1" Range("A1") = GetValue(p, f, s, a) End Sub I thought, by simply reversing the values on either side fo te equality sigh would work: GetValue(p, f, s, a) = Range("A1") Unfortunately, it doesn't work... When I run the code like this, I get a message that says: Run Time Error 424 Object Required Does anyone have any thoughts on this? Thanks, Ryan--- -- RyGuy "ryguy7272" wrote: I am trying to find a way to send a range (H4:J6) of data from one worksheet, which is saved on my desktop, but could be stored anywhere, to a specific sheet on my firm's network (intranet). Basically, I am trying to write to a closed workbook on a network drive. I read this resource: http://www.erlandsendata.no/english/...=envbadacrs2ws I guess it can be done, but I not sure how to actually implement the procedure. I would sincerely appreciate any help that anyone could offer. The network UNC is: \\fsrv3\public\Forecast Regards, Ryan--- -- RyGuy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send Data From Active Sheet to Closed Sheet on Network
I just found this resource:
http://www.j-walk.com/ss/excel/tips/tip82.htm This seems to do pretty much what I want to do, but how can I get it to UPLOAD the data to a file saved on the network? The code that I am working with is below: Private Function GetValue(path, file, sheet, ref) ' Retrieves a value from a closed workbook Dim arg As String ' Make sure the file exists If Right(path, 1) < "\" Then path = path & "\" If Dir(path & file) = "" Then GetValue = "File Not Found" Exit Function End If ' Create the argument arg = "'" & path & "[" & file & "]" & sheet & "'!" & _ Range(ref).Range("A1").Address(, , xlR1C1) ' Execute an XLM macro GetValue = ExecuteExcel4Macro(arg) End Function Sub TestGetValue() p = "\\fsrv3\public\Forecast\Display Forecast" f = "Destination.xls" s = "Sheet1" a = "A1" Range("A1") = GetValue(p, f, s, a) End Sub I thought, by simply reversing the values on either side fo te equality sigh would work: GetValue(p, f, s, a) = Range("A1") Unfortunately, it doesn't work... When I run the code like this, I get a message that says: Run Time Error 424 Object Required Does anyone have any thoughts on this? Thanks, Ryan--- -- RyGuy "ryguy7272" wrote: I am trying to find a way to send a range (H4:J6) of data from one worksheet, which is saved on my desktop, but could be stored anywhere, to a specific sheet on my firm's network (intranet). Basically, I am trying to write to a closed workbook on a network drive. I read this resource: http://www.erlandsendata.no/english/...=envbadacrs2ws I guess it can be done, but I not sure how to actually implement the procedure. I would sincerely appreciate any help that anyone could offer. The network UNC is: \\fsrv3\public\Forecast Regards, Ryan--- -- RyGuy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send Data From Active Sheet to Closed Sheet on Network
Sorry for that last double-post........
This Ron de Bruin code also pretty much does what I want: Sub File_In_Network_Folder() Application.ScreenUpdating = False On Error Resume Next 'Call the macro GetRange GetRange "\\fsrv3\public\Forecast", "Destination.xls", "Sheet1", "A1:B4", _ Sheets("Sheet1").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 However, I want to send the data from: Sheets("Sheet1").Range("A1") to: "\\fsrv3\public\Forecast", "Destination.xls", "Sheet1", "A1:B4" Or, something along those lines. I would really do something like this: from: Sheets("Sheet1").Range("H4:J6") to: "\\fsrv3\public\Forecast", "Destination.xls", "Sheet1", " H4:J6" Thanks, Ryan--- -- RyGuy "ryguy7272" wrote: I just found this resource: http://www.j-walk.com/ss/excel/tips/tip82.htm This seems to do pretty much what I want to do, but how can I get it to UPLOAD the data to a file saved on the network? The code that I am working with is below: Private Function GetValue(path, file, sheet, ref) ' Retrieves a value from a closed workbook Dim arg As String ' Make sure the file exists If Right(path, 1) < "\" Then path = path & "\" If Dir(path & file) = "" Then GetValue = "File Not Found" Exit Function End If ' Create the argument arg = "'" & path & "[" & file & "]" & sheet & "'!" & _ Range(ref).Range("A1").Address(, , xlR1C1) ' Execute an XLM macro GetValue = ExecuteExcel4Macro(arg) End Function Sub TestGetValue() p = "\\fsrv3\public\Forecast\Display Forecast" f = "Destination.xls" s = "Sheet1" a = "A1" Range("A1") = GetValue(p, f, s, a) End Sub I thought, by simply reversing the values on either side fo te equality sigh would work: GetValue(p, f, s, a) = Range("A1") Unfortunately, it doesn't work... When I run the code like this, I get a message that says: Run Time Error 424 Object Required Does anyone have any thoughts on this? Thanks, Ryan--- -- RyGuy "ryguy7272" wrote: I am trying to find a way to send a range (H4:J6) of data from one worksheet, which is saved on my desktop, but could be stored anywhere, to a specific sheet on my firm's network (intranet). Basically, I am trying to write to a closed workbook on a network drive. I read this resource: http://www.erlandsendata.no/english/...=envbadacrs2ws I guess it can be done, but I not sure how to actually implement the procedure. I would sincerely appreciate any help that anyone could offer. The network UNC is: \\fsrv3\public\Forecast Regards, Ryan--- -- RyGuy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send Data From Active Sheet to Closed Sheet on Network
Finally found my answer he
http://www.microsoft.com/office/comm...r=US&sloc=&p=1 Hope others may benefit from this!! Ryan-- -- RyGuy "ryguy7272" wrote: Sorry for that last double-post........ This Ron de Bruin code also pretty much does what I want: Sub File_In_Network_Folder() Application.ScreenUpdating = False On Error Resume Next 'Call the macro GetRange GetRange "\\fsrv3\public\Forecast", "Destination.xls", "Sheet1", "A1:B4", _ Sheets("Sheet1").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 However, I want to send the data from: Sheets("Sheet1").Range("A1") to: "\\fsrv3\public\Forecast", "Destination.xls", "Sheet1", "A1:B4" Or, something along those lines. I would really do something like this: from: Sheets("Sheet1").Range("H4:J6") to: "\\fsrv3\public\Forecast", "Destination.xls", "Sheet1", " H4:J6" Thanks, Ryan--- -- RyGuy "ryguy7272" wrote: I just found this resource: http://www.j-walk.com/ss/excel/tips/tip82.htm This seems to do pretty much what I want to do, but how can I get it to UPLOAD the data to a file saved on the network? The code that I am working with is below: Private Function GetValue(path, file, sheet, ref) ' Retrieves a value from a closed workbook Dim arg As String ' Make sure the file exists If Right(path, 1) < "\" Then path = path & "\" If Dir(path & file) = "" Then GetValue = "File Not Found" Exit Function End If ' Create the argument arg = "'" & path & "[" & file & "]" & sheet & "'!" & _ Range(ref).Range("A1").Address(, , xlR1C1) ' Execute an XLM macro GetValue = ExecuteExcel4Macro(arg) End Function Sub TestGetValue() p = "\\fsrv3\public\Forecast\Display Forecast" f = "Destination.xls" s = "Sheet1" a = "A1" Range("A1") = GetValue(p, f, s, a) End Sub I thought, by simply reversing the values on either side fo te equality sigh would work: GetValue(p, f, s, a) = Range("A1") Unfortunately, it doesn't work... When I run the code like this, I get a message that says: Run Time Error 424 Object Required Does anyone have any thoughts on this? Thanks, Ryan--- -- RyGuy "ryguy7272" wrote: I am trying to find a way to send a range (H4:J6) of data from one worksheet, which is saved on my desktop, but could be stored anywhere, to a specific sheet on my firm's network (intranet). Basically, I am trying to write to a closed workbook on a network drive. I read this resource: http://www.erlandsendata.no/english/...=envbadacrs2ws I guess it can be done, but I not sure how to actually implement the procedure. I would sincerely appreciate any help that anyone could offer. The network UNC is: \\fsrv3\public\Forecast Regards, Ryan--- -- RyGuy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Send Values from TextBoxes to Specific Sheet on Network? | Excel Programming | |||
Adding data to a closed sheet | Excel Programming | |||
Consolidation of data from cell in active sheet of closed workbook | Excel Worksheet Functions | |||
Copy from active sheet and paste into new sheet using info from cell in active | Excel Programming | |||
Send Email (Active Sheet) | Excel Programming |