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 ")
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.
"ryguy7272" wrote:
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---
--
RyGuy
"Joel" wrote:
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\Destinatio n.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)
"ryguy7272" wrote:
Thanks Joel! Just did it. Now I get this message:
Compile Error:
ByRef argument type mismatch
Any other ideas?
Thanks,
Ryan---
--
RyGuy
"Joel" wrote:
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