R
ryguy7272
I am trying to write data to a closed file.
I found some great information from this link:
http://www.erlandsendata.no/english/index.php?d=envbadacrs2ws
Form an Excel file on my desktop, I am trying to call the procedure as such:
Sub SenData()
Application.ScreenUpdating = False ' turn off screen updating
Workbooks.Open ("\\fsrv3\public\Forecast\Destination.xls") ' open a workbook
' write the content of the recordset to the workbook
RS2WS rs, Range("A3") ' rs is an ADO recordset variable
Workbooks.Close ("\\fsrv3\public\Forecast\Destination.xls") 'True save and
close the workbook
Application.ScreenUpdating = True ' turn on screen updating
End Sub
Sub RS2WS(rs As ADODB.Recordset, TargetCell As Range)
Dim f As Integer, r As Long, c As Long
If rs Is Nothing Then Exit Sub
If rs.State <> adStateOpen Then Exit Sub
If TargetCell Is Nothing Then Exit Sub
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.StatusBar = "Writing data from recordset..."
End With
With TargetCell.Cells(1, 1)
r = .Row
c = .Column
End With
With TargetCell.Parent
.Range(.Cells(r, c), .Cells(.Rows.Count, c + rs.Fields.Count -
1)).Clear
' clear existing contents
' write column headers
For f = 0 To rs.Fields.Count - 1
On Error Resume Next
.Cells(r, c + f).Formula = rs.Fields(f).Name
On Error GoTo 0
Next f
' write records
On Error Resume Next
rs.MoveFirst
On Error GoTo 0
Do While Not rs.EOF
r = r + 1
For f = 0 To rs.Fields.Count - 1
On Error Resume Next
.Cells(r, c + f).Formula = rs.Fields(f).Value
On Error GoTo 0
Next f
rs.MoveNext
Loop
.Rows(TargetCell.Cells(1, 1).Row).Font.Bold = True
.Columns("A:IV").AutoFit
End With
With Application
.StatusBar = False
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub
What I am trying to do is simply take a value in a cell (could be any cell,
let’s say A3), from a sheet (could be any sheet) and transfer it a certain
cell in a certain sheet saved on a network drive.
When I try to run the Sub, I get a ‘Compile Error, ByRef argument type
mismatch’ message. This line seems to cause the error.
RS2WS rs, Range("A3")
The file on the network drive is called ‘Destination.xls’
I would appreciate any help with calling this Sub and basically getting this
thing working. I know how to write data to closed files; I think the network
drive thing is screwing me up. I just can't figure out how to do this...
Regards,
Ryan--
I found some great information from this link:
http://www.erlandsendata.no/english/index.php?d=envbadacrs2ws
Form an Excel file on my desktop, I am trying to call the procedure as such:
Sub SenData()
Application.ScreenUpdating = False ' turn off screen updating
Workbooks.Open ("\\fsrv3\public\Forecast\Destination.xls") ' open a workbook
' write the content of the recordset to the workbook
RS2WS rs, Range("A3") ' rs is an ADO recordset variable
Workbooks.Close ("\\fsrv3\public\Forecast\Destination.xls") 'True save and
close the workbook
Application.ScreenUpdating = True ' turn on screen updating
End Sub
Sub RS2WS(rs As ADODB.Recordset, TargetCell As Range)
Dim f As Integer, r As Long, c As Long
If rs Is Nothing Then Exit Sub
If rs.State <> adStateOpen Then Exit Sub
If TargetCell Is Nothing Then Exit Sub
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.StatusBar = "Writing data from recordset..."
End With
With TargetCell.Cells(1, 1)
r = .Row
c = .Column
End With
With TargetCell.Parent
.Range(.Cells(r, c), .Cells(.Rows.Count, c + rs.Fields.Count -
1)).Clear
' clear existing contents
' write column headers
For f = 0 To rs.Fields.Count - 1
On Error Resume Next
.Cells(r, c + f).Formula = rs.Fields(f).Name
On Error GoTo 0
Next f
' write records
On Error Resume Next
rs.MoveFirst
On Error GoTo 0
Do While Not rs.EOF
r = r + 1
For f = 0 To rs.Fields.Count - 1
On Error Resume Next
.Cells(r, c + f).Formula = rs.Fields(f).Value
On Error GoTo 0
Next f
rs.MoveNext
Loop
.Rows(TargetCell.Cells(1, 1).Row).Font.Bold = True
.Columns("A:IV").AutoFit
End With
With Application
.StatusBar = False
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub
What I am trying to do is simply take a value in a cell (could be any cell,
let’s say A3), from a sheet (could be any sheet) and transfer it a certain
cell in a certain sheet saved on a network drive.
When I try to run the Sub, I get a ‘Compile Error, ByRef argument type
mismatch’ message. This line seems to cause the error.
RS2WS rs, Range("A3")
The file on the network drive is called ‘Destination.xls’
I would appreciate any help with calling this Sub and basically getting this
thing working. I know how to write data to closed files; I think the network
drive thing is screwing me up. I just can't figure out how to do this...
Regards,
Ryan--