Upload Data from Sheet1 on Local Machine to Sheet1 on LAN

[ryguy7272]

Upload Data from Sheet1 on Local Machine to Sheet1 on LAN

I am trying to modify the Ron De Bruin code that I found here:
http://www.rondebruin.nl/copy7.htm

Basically, I am trying to upload data from a range ("A1:B4") in Sheet1 on my
computer to a file saved on a LAN. I am trying to use the code below, but it
needs to be modified to SEND the data to the file on the LAN, not RECEIVE the
data from the file on the LAN.

I am guessing that the Sub GetRange is ordered incorrectly, but I can’t
figure out how to straighten it out. If someone sees the error please let
me know. Also, let me know if you need any clarification on anything.

Sub File_In_Network_Folder()
Application.ScreenUpdating = False
On Error Resume Next



'Call the macro GetRange
GetRange Sheets("Sheet1").Range("A1"), _
"\\fsrv3\public\Sales Operations\Ryan", _
"Destination.xls", _
"Sheet1", _
Range("A1:B4")



On Error GoTo 0
Application.ScreenUpdating = True
End Sub


Sub GetRange(SourceRange As String, _
FilePath As String, _
FileName As String, _
SheetName 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


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


Regards,
Ryan---

 

[Keith]

Upload Data from Sheet1 on Local Machine to Sheet1 on LAN

I am trying to modify the Ron De Bruin code that I found here:
http://www.rondebruin.nl/copy7.htm

Basically, I am trying to upload data from a range ("A1:B4") in Sheet1 on
my
computer to a file saved on a LAN. I am trying to use the code below, but
it
needs to be modified to SEND the data to the file on the LAN, not RECEIVE
the
data from the file on the LAN.

I am guessing that the Sub GetRange is ordered incorrectly, but I can't
figure out how to straighten it out. If someone sees the error please let
me know. Also, let me know if you need any clarification on anything.

Sub File_In_Network_Folder()
Application.ScreenUpdating = False
On Error Resume Next



'Call the macro GetRange
GetRange Sheets("Sheet1").Range("A1"), _
"\\fsrv3\public\Sales Operations\Ryan", _
"Destination.xls", _
"Sheet1", _
Range("A1:B4")



On Error GoTo 0
Application.ScreenUpdating = True
End Sub


Sub GetRange(SourceRange As String, _
FilePath As String, _
FileName As String, _
SheetName 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


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


Regards,
Ryan---

not sure about the other routine, but this copies to a network file

Sub SaveValuesToNetworkFile()
Dim p As String ' path to file
Dim f As String ' file name
Dim s As String ' sheet name
Dim r As String ' range
Dim ws As Worksheet ' worksheet where values will be saved
' assign network path and don't forget the final \
p = "n:\path\"
' assign filename
f = "file.xls"
' assign sheet name
s = "Sheet1"
' assign range
r = "A1:A4"
' set variable for current worksheet
Set ws = ActiveSheet
' open network file
Workbooks.Open (p & f)
' copy range values from current worksheet to same range in network file
ws.Range(r).Copy Workbooks(f).Worksheets(s).Range(r)
' close network file, saving changes
Workbooks(f).Close True
End Sub

 

[ryguy7272]

Exactly what I was looking for! Thank you so much Keith!!
Regards,
Ryan---


--
RyGuy

Upload Data from Sheet1 on Local Machine to Sheet1 on LAN

I am trying to modify the Ron De Bruin code that I found here:
http://www.rondebruin.nl/copy7.htm

Basically, I am trying to upload data from a range ("A1:B4") in Sheet1 on
my
computer to a file saved on a LAN. I am trying to use the code below, but
it
needs to be modified to SEND the data to the file on the LAN, not RECEIVE
the
data from the file on the LAN.

I am guessing that the Sub GetRange is ordered incorrectly, but I can't
figure out how to straighten it out. If someone sees the error please let
me know. Also, let me know if you need any clarification on anything.

Sub File_In_Network_Folder()
Application.ScreenUpdating = False
On Error Resume Next



'Call the macro GetRange
GetRange Sheets("Sheet1").Range("A1"), _
"\\fsrv3\public\Sales Operations\Ryan", _
"Destination.xls", _
"Sheet1", _
Range("A1:B4")



On Error GoTo 0
Application.ScreenUpdating = True
End Sub


Sub GetRange(SourceRange As String, _
FilePath As String, _
FileName As String, _
SheetName 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


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


Regards,
Ryan---

not sure about the other routine, but this copies to a network file

Sub SaveValuesToNetworkFile()
Dim p As String ' path to file
Dim f As String ' file name
Dim s As String ' sheet name
Dim r As String ' range
Dim ws As Worksheet ' worksheet where values will be saved
' assign network path and don't forget the final \
p = "n:\path\"
' assign filename
f = "file.xls"
' assign sheet name
s = "Sheet1"
' assign range
r = "A1:A4"
' set variable for current worksheet
Set ws = ActiveSheet
' open network file
Workbooks.Open (p & f)
' copy range values from current worksheet to same range in network file
ws.Range(r).Copy Workbooks(f).Worksheets(s).Range(r)
' close network file, saving changes
Workbooks(f).Close True
End Sub