write data to a closed file on network drive

  • Thread starter Thread starter ryguy7272
  • Start date Start date
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--
 
You need to add two libraries

Microsoft ActiveX Data Objects 2.8 library
Microsoft ActiveX data Objects Recordset 2.8 library

from VBA Menu - tools references. Check the above two items or latest
version of the library on your PC.
 
Thanks Joel! Just did it. Now I get this message:
Compile Error:
ByRef argument type mismatch


Any other ideas?


Thanks,
Ryan---
 
I did 2 more things

1) Modified SenData
2) Added ByVal to RSWWS

RS is not defined that is why I had to add ByVal to the RS2WS. This code
will not run without having an RS object. Because RS is not defined RS will
= nothing and the code will exit.

Sub SenData()

Application.ScreenUpdating = False ' turn off screen updating
Set newbk = Workbooks.Open("\\fsrv3\public\Forecast\Destination.xls") '
open a workbook
' write the content of the recordset to the workbook
Call RS2WS(rs, Range("A3")) ' rs is an ADO recordset variable
newbk.Close '("\\fsrv3\public\Forecast\Destination.xls") True save and
'close the workbook
Application.ScreenUpdating = True ' turn on screen updating

End Sub

Sub RS2WS(ByVal rs As ADODB.Recordset, TargetCell As Range)
 
Thanks, again for helping out Joel!! I think I am almost there!! When I ran
the macro, Excel paused for a moment, so I guess it was going to the network
to locate the file. Then after, probably 5 seconds or so, I got this message:
Run-time error ‘424’
Object Required.

This line is yellow:
Call RS2WS(rs, Range("A3")) ' rs is an ADO recordset variable


What kind of object is required now?
What do I need to do?


Ryan---
 
First, I just found the reference to the library at the bottom of the website
you had listed in your first posting.

I'm a little confused at what you are trying to do. An ADO is a access
database which contains tables similar to excel. When you use the Macro
language in Access you can open excel files and read them, but you read them
as a table. The table refereces only allow you to move the record set one
row at a time. with Excel you can reference each row directly. Example, in
Access tabe to get to row 1000 you have to exceute the instruction get Next
Record set 1000 times. In excel you just say get Row 1000.

I'm not usre the right way you would need to open the XLS file to get this
macro to run. You may just need to pass the variable newbk (from my code) or
you may need to open the workbook as follows

from
Set newbk = Workbooks.Open("\\fsrv3\public\Forecast\Destination.xls")
to
Set newbk = GetObject("\\fsrv3\public\Forecast\Destination.xls")


I did a little bit of programming in Access VBA where I did open an Excel
File. It was a few years ago. You would better off getting information from
the Access Programming group.
 
Thanks, but that link really doesn't help me very much...sorry...

I use ADO all the time for getting Access to communicate with Word and
Excel. I don't know much about the technical specs of ADO, but I think ADO
works just for Excel too, right.


When I run the code, I get this...
Message is:
Run-Time Error ‘424’:
Object Required

The code still doesn’t work; I can’t imagine what it is now. It looks
right, but it fails on this line:
Call RS2WS(rs, Range("A3"))

Sub SenData()

Application.ScreenUpdating = False
Set newbk = GetObject("\\fsrv3\public\Forecast\Destination.xls")

Call RS2WS(rs, Range("A3"))
newbk.Close

Application.ScreenUpdating = True

End Sub

Sub RS2WS(ByVal 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


The references are now:
Visual Basic for Applications
Microsoft Excel 11.0 Object Library
OLE Automation
Microsoft Office 11.0 Object library
Microsoft ADO Ext. 2.8 for DDL and Security
Microsoft Active X Data Objects 2.8 Library
Microsoft Active X Data Objects Recordset 2.8 Library
These are set for BOTH excel files; the one on my desktop (which I am trying
to get the value in Cell A3 to the Excel file on the network) and the Excel
file on the network (Destination.xls)


Thanks for the time you have spent on this already!!
Do you have any other ideas Joel? Anyone?



Regards,
Ryan---
 
I went back to the website and modified your code like I recommended yesterday

Sub SenData()

strSourceFile = "\\fsrv3\public\Forecast\Destination.xls"
Set cn = New ADODB.Connection
On Error Resume Next
cn.Open "DRIVER={Microsoft Excel Driver (*.xls)};DriverId=790;" & _
"ReadOnly=True;DBQ=" & strSourceFile & ";"
' DriverId=790: Excel 97/2000
' DriverId=22: Excel 5/95
' DriverId=278: Excel 4
' DriverId=534: Excel 3
On Error GoTo 0
If cn Is Nothing Then
MsgBox "Can't find the file!", vbExclamation, ThisWorkbook.Name
Exit Sub
End If

' open a recordset
Set rs = New ADODB.Recordset
On Error Resume Next
rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText


Call RS2WS(rs, Range("A3"))
cn.Close

Application.ScreenUpdating = True

End Sub

Sub RS2WS(ByVal 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
 
Thanks for the follow up Joel! The error is gone, but the no data is sent to
the 'Destination' sheet. As the macro runs, Excel pauses for a moment, but
then it seems like nothing happens...the data in the 'Destination' sheet
still is not updated when I run the code.

I feel like the UNC path should be more defined, like this:
strSourceFile =
"\\fsrv3\public\Forecast\Sheets(""Destination"").Range(""A3"").Value"

How does Excel know where to put the data from the source file (cell A3) to
the destination file?

This is pretty technical for me (I'm used to working on my C: drive) I can't
imagine what the problem is now. Did you get this working for yourself Joel?

Thanks,
Ryan--
 
The recordset was returning nothing you this instead. Use the correct sheet
name and include a dollar sign at the end of the name.

from
' open a recordset
Set rs = New ADODB.Recordset
On Error Resume Next
rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText

to
' open a recordset
Set rs = New ADODB.Recordset

rs.Open "SELECT * FROM [Sheet2$]", _
cn, adOpenForwardOnly, adLockReadOnly, adCmdText
 
Damn! This is pretty sweet! It takes all the data in the file on the
network, and imports it into my spreadsheet (saved on my desktop), starting
in cell A3 and filling to the right and filling down! I am glad to have this
code and will certainly find a use for it in the future! There is one thing
however, I wanted to take data from a cell in a file on my desktop and
transfer that to the file on the network. Essentially, I wanted the code to
do the opposite of what this code does. Is that possible? I’m pretty sure
it is, but I don’t have any experience doing this and I’m fairly new to the
whole UNC thing. In short, I’d like to take the value in cell A3 in a
certain spreadsheet and transfer that to a specific cell in a specific sheet,
maybe cell A3 but it could be any cell, in the file on the network.

Thanks for everything Joel!!! If you think it is possible to get the code
to do the opposite of what it is doing now, please let me know.

Regards,
Ryan---



--
RyGuy


Joel said:
The recordset was returning nothing you this instead. Use the correct sheet
name and include a dollar sign at the end of the name.

from
' open a recordset
Set rs = New ADODB.Recordset
On Error Resume Next
rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText

to
' open a recordset
Set rs = New ADODB.Recordset

rs.Open "SELECT * FROM [Sheet2$]", _
cn, adOpenForwardOnly, adLockReadOnly, adCmdText

ryguy7272 said:
Thanks for the follow up Joel! The error is gone, but the no data is sent to
the 'Destination' sheet. As the macro runs, Excel pauses for a moment, but
then it seems like nothing happens...the data in the 'Destination' sheet
still is not updated when I run the code.

I feel like the UNC path should be more defined, like this:
strSourceFile =
"\\fsrv3\public\Forecast\Sheets(""Destination"").Range(""A3"").Value"

How does Excel know where to put the data from the source file (cell A3) to
the destination file?

This is pretty technical for me (I'm used to working on my C: drive) I can't
imagine what the problem is now. Did you get this working for yourself Joel?

Thanks,
Ryan--
Click to expand...
Click to expand...
 
go back to the original website on this sheet

http://www.erlandsendata.no/english/index.php?t=envbadac

I not an expert on the ADO (SQL) language. Only have done a little bit of
work and have the concept not the syntec.

ryguy7272 said:
Damn! This is pretty sweet! It takes all the data in the file on the
network, and imports it into my spreadsheet (saved on my desktop), starting
in cell A3 and filling to the right and filling down! I am glad to have this
code and will certainly find a use for it in the future! There is one thing
however, I wanted to take data from a cell in a file on my desktop and
transfer that to the file on the network. Essentially, I wanted the code to
do the opposite of what this code does. Is that possible? I’m pretty sure
it is, but I don’t have any experience doing this and I’m fairly new to the
whole UNC thing. In short, I’d like to take the value in cell A3 in a
certain spreadsheet and transfer that to a specific cell in a specific sheet,
maybe cell A3 but it could be any cell, in the file on the network.

Thanks for everything Joel!!! If you think it is possible to get the code
to do the opposite of what it is doing now, please let me know.

Regards,
Ryan---



--
RyGuy


Joel said:
The recordset was returning nothing you this instead. Use the correct sheet
name and include a dollar sign at the end of the name.

from
' open a recordset
Set rs = New ADODB.Recordset
On Error Resume Next
rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText

to
' open a recordset
Set rs = New ADODB.Recordset

rs.Open "SELECT * FROM [Sheet2$]", _
cn, adOpenForwardOnly, adLockReadOnly, adCmdText

ryguy7272 said:
Thanks for the follow up Joel! The error is gone, but the no data is sent to
the 'Destination' sheet. As the macro runs, Excel pauses for a moment, but
then it seems like nothing happens...the data in the 'Destination' sheet
still is not updated when I run the code.

I feel like the UNC path should be more defined, like this:
strSourceFile =
"\\fsrv3\public\Forecast\Sheets(""Destination"").Range(""A3"").Value"

How does Excel know where to put the data from the source file (cell A3) to
the destination file?

This is pretty technical for me (I'm used to working on my C: drive) I can't
imagine what the problem is now. Did you get this working for yourself Joel?

Thanks,
Ryan--

--
RyGuy


:

I went back to the website and modified your code like I recommended yesterday

Sub SenData()

strSourceFile = "\\fsrv3\public\Forecast\Destination.xls"
Set cn = New ADODB.Connection
On Error Resume Next
cn.Open "DRIVER={Microsoft Excel Driver (*.xls)};DriverId=790;" & _
"ReadOnly=True;DBQ=" & strSourceFile & ";"
' DriverId=790: Excel 97/2000
' DriverId=22: Excel 5/95
' DriverId=278: Excel 4
' DriverId=534: Excel 3
On Error GoTo 0
If cn Is Nothing Then
MsgBox "Can't find the file!", vbExclamation, ThisWorkbook.Name
Exit Sub
End If

' open a recordset
Set rs = New ADODB.Recordset
On Error Resume Next
rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText


Call RS2WS(rs, Range("A3"))
cn.Close

Application.ScreenUpdating = True

End Sub

Sub RS2WS(ByVal 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


:

Thanks, but that link really doesn't help me very much...sorry...

I use ADO all the time for getting Access to communicate with Word and
Excel. I don't know much about the technical specs of ADO, but I think ADO
works just for Excel too, right.


When I run the code, I get this...
Message is:
Run-Time Error ‘424’:
Object Required

The code still doesn’t work; I can’t imagine what it is now. It looks
right, but it fails on this line:
Call RS2WS(rs, Range("A3"))

Sub SenData()

Application.ScreenUpdating = False
Set newbk = GetObject("\\fsrv3\public\Forecast\Destination.xls")

Call RS2WS(rs, Range("A3"))
newbk.Close

Application.ScreenUpdating = True

End Sub

Sub RS2WS(ByVal 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


The references are now:
Visual Basic for Applications
Microsoft Excel 11.0 Object Library
OLE Automation
Microsoft Office 11.0 Object library
Microsoft ADO Ext. 2.8 for DDL and Security
Microsoft Active X Data Objects 2.8 Library
Microsoft Active X Data Objects Recordset 2.8 Library
These are set for BOTH excel files; the one on my desktop (which I am trying
to get the value in Cell A3 to the Excel file on the network) and the Excel
file on the network (Destination.xls)


Thanks for the time you have spent on this already!!
Do you have any other ideas Joel? Anyone?



Regards,
Ryan---


--
RyGuy


:

There was a link on your web posting site that may help

http://www.erlandsendata.no/english/index.php?d=envbadacwbdbado

:

First, I just found the reference to the library at the bottom of the website
you had listed in your first posting.

I'm a little confused at what you are trying to do. An ADO is a access
database which contains tables similar to excel. When you use the Macro
language in Access you can open excel files and read them, but you read them
as a table. The table refereces only allow you to move the record set one
row at a time. with Excel you can reference each row directly. Example, in
Access tabe to get to row 1000 you have to exceute the instruction get Next
Record set 1000 times. In excel you just say get Row 1000.

I'm not usre the right way you would need to open the XLS file to get this
macro to run. You may just need to pass the variable newbk (from my code) or
you may need to open the workbook as follows

from
Set newbk = Workbooks.Open("\\fsrv3\public\Forecast\Destination.xls")
to
Set newbk = GetObject("\\fsrv3\public\Forecast\Destination.xls")


I did a little bit of programming in Access VBA where I did open an Excel
File. It was a few years ago. You would better off getting information from
the Access Programming group.
:

Thanks, again for helping out Joel!! I think I am almost there!! When I ran
the macro, Excel paused for a moment, so I guess it was going to the network
to locate the file. Then after, probably 5 seconds or so, I got this message:
Run-time error ‘424’
Click to expand...
Click to expand...
Click to expand...
 
Thanks for helping me Joel! Yes, I will look at that site more and try to
figure it out myself. Thanks again!!


Ryan--

--
RyGuy


Joel said:
go back to the original website on this sheet

http://www.erlandsendata.no/english/index.php?t=envbadac

I not an expert on the ADO (SQL) language. Only have done a little bit of
work and have the concept not the syntec.

ryguy7272 said:
Damn! This is pretty sweet! It takes all the data in the file on the
network, and imports it into my spreadsheet (saved on my desktop), starting
in cell A3 and filling to the right and filling down! I am glad to have this
code and will certainly find a use for it in the future! There is one thing
however, I wanted to take data from a cell in a file on my desktop and
transfer that to the file on the network. Essentially, I wanted the code to
do the opposite of what this code does. Is that possible? I’m pretty sure
it is, but I don’t have any experience doing this and I’m fairly new to the
whole UNC thing. In short, I’d like to take the value in cell A3 in a
certain spreadsheet and transfer that to a specific cell in a specific sheet,
maybe cell A3 but it could be any cell, in the file on the network.

Thanks for everything Joel!!! If you think it is possible to get the code
to do the opposite of what it is doing now, please let me know.

Regards,
Ryan---



--
RyGuy


Joel said:
The recordset was returning nothing you this instead. Use the correct sheet
name and include a dollar sign at the end of the name.

from
' open a recordset
Set rs = New ADODB.Recordset
On Error Resume Next
rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText

to
' open a recordset
Set rs = New ADODB.Recordset

rs.Open "SELECT * FROM [Sheet2$]", _
cn, adOpenForwardOnly, adLockReadOnly, adCmdText

:

Thanks for the follow up Joel! The error is gone, but the no data is sent to
the 'Destination' sheet. As the macro runs, Excel pauses for a moment, but
then it seems like nothing happens...the data in the 'Destination' sheet
still is not updated when I run the code.

I feel like the UNC path should be more defined, like this:
strSourceFile =
"\\fsrv3\public\Forecast\Sheets(""Destination"").Range(""A3"").Value"

How does Excel know where to put the data from the source file (cell A3) to
the destination file?

This is pretty technical for me (I'm used to working on my C: drive) I can't
imagine what the problem is now. Did you get this working for yourself Joel?

Thanks,
Ryan--

--
RyGuy


:

I went back to the website and modified your code like I recommended yesterday

Sub SenData()

strSourceFile = "\\fsrv3\public\Forecast\Destination.xls"
Set cn = New ADODB.Connection
On Error Resume Next
cn.Open "DRIVER={Microsoft Excel Driver (*.xls)};DriverId=790;" & _
"ReadOnly=True;DBQ=" & strSourceFile & ";"
' DriverId=790: Excel 97/2000
' DriverId=22: Excel 5/95
' DriverId=278: Excel 4
' DriverId=534: Excel 3
On Error GoTo 0
If cn Is Nothing Then
MsgBox "Can't find the file!", vbExclamation, ThisWorkbook.Name
Exit Sub
End If

' open a recordset
Set rs = New ADODB.Recordset
On Error Resume Next
rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText


Call RS2WS(rs, Range("A3"))
cn.Close

Application.ScreenUpdating = True

End Sub

Sub RS2WS(ByVal 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


:

Thanks, but that link really doesn't help me very much...sorry...

I use ADO all the time for getting Access to communicate with Word and
Excel. I don't know much about the technical specs of ADO, but I think ADO
works just for Excel too, right.


When I run the code, I get this...
Message is:
Run-Time Error ‘424’:
Object Required

The code still doesn’t work; I can’t imagine what it is now. It looks
right, but it fails on this line:
Call RS2WS(rs, Range("A3"))

Sub SenData()

Application.ScreenUpdating = False
Set newbk = GetObject("\\fsrv3\public\Forecast\Destination.xls")

Call RS2WS(rs, Range("A3"))
newbk.Close

Application.ScreenUpdating = True

End Sub

Sub RS2WS(ByVal 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


The references are now:
Visual Basic for Applications
Microsoft Excel 11.0 Object Library
OLE Automation
Microsoft Office 11.0 Object library
Microsoft ADO Ext. 2.8 for DDL and Security
Microsoft Active X Data Objects 2.8 Library
Microsoft Active X Data Objects Recordset 2.8 Library
These are set for BOTH excel files; the one on my desktop (which I am trying
to get the value in Cell A3 to the Excel file on the network) and the Excel
file on the network (Destination.xls)


Thanks for the time you have spent on this already!!
Do you have any other ideas Joel? Anyone?



Regards,
Ryan---


--
RyGuy


:

There was a link on your web posting site that may help

http://www.erlandsendata.no/english/index.php?d=envbadacwbdbado

:

First, I just found the reference to the library at the bottom of the website
you had listed in your first posting.

I'm a little confused at what you are trying to do. An ADO is a access
database which contains tables similar to excel. When you use the Macro
language in Access you can open excel files and read them, but you read them
as a table. The table refereces only allow you to move the record set one
row at a time. with Excel you can reference each row directly. Example, in
Access tabe to get to row 1000 you have to exceute the instruction get Next
Record set 1000 times. In excel you just say get Row 1000.

I'm not usre the right way you would need to open the XLS file to get this
macro to run. You may just need to pass the variable newbk (from my code) or
you may need to open the workbook as follows

from
Set newbk = Workbooks.Open("\\fsrv3\public\Forecast\Destination.xls")
to
Set newbk = GetObject("\\fsrv3\public\Forecast\Destination.xls")
Click to expand...
Click to expand...
Click to expand...
 
Back
Top