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" wrote:

go back to the original website on this sheet

http://www.erlandsendata.no/english/...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" wrote:

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? Im pretty sure
it is, but I dont have any experience doing this and Im fairly new to the
whole UNC thing. In short, Id 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" wrote:

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" wrote:

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"").R ange(""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


"Joel" wrote:

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


"ryguy7272" wrote:

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 doesnt work; I cant 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


"Joel" wrote:

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

http://www.erlandsendata.no/english/...nvbadacwbdbado

"Joel" wrote:

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\Destinatio n.xls")
to
Set newbk = GetObject("\\fsrv3\public\Forecast\Destination.xls ")