Access Into Excel issue

[SpankYou]

Hey,

I know similar things have been posted before, but i can't find out exactly how to do my problem.

I have an excel template, with a header (to make it look pretty). Basically I need all the data to be entered starting from cell A3. The data is coming from a form which is run off of a query. Currently I have something like this

Dim xlobj As Excel.Application
Set xlobj = CreateObject("excel.application&quot
With xlobj

.Workbooks.Open Filename:="C:\WINDOWS\Desktop\CableScheduleTemplate.xlt"
.Range("A3&quot.Select
.ActiveCell.FormulaR1C1 = CableID
.Range("B3&quot.Select
.ActiveCell.FormulaR1C1 = CableType
.Range("C3&quot.Select
.ActiveCell.FormulaR1C1 = FromLocation
.Range("D3&quot.Select
.ActiveCell.FormulaR1C1 = FromUnit
.Range("E3&quot.Select
.ActiveCell.FormulaR1C1 = FromTermination
.Range("F3&quot.Select
.ActiveCell.FormulaR1C1 = FromWiringDetail
.Range("G3&quot.Select
.ActiveCell.FormulaR1C1 = ToLocation
.Range("H3&quot.Select
.ActiveCell.FormulaR1C1 = ToUnit
.Range("I3&quot.Select
.ActiveCell.FormulaR1C1 = ToTermination
.Range("J3&quot.Select
.ActiveCell.FormulaR1C1 = ToWiringDetail
.Range("K3&quot.Select
.ActiveCell.FormulaR1C1 = CableFunction
'and so on
.Quit


This only puts the first record into the selected rows though.

Any ideas would be very appreciated.

Thanks

Sam

"You couldn't fool your mother on the foolingest day of your life if you had an electrified fooling machine." - Homer

 

[Turpis]

Hey Sam, not really sure why your code isn't working! You might try instead of having Excel actually select cells to place data into them, just have it shoot the data straight into the cells. The code will execute much faster. Here is an example.

.Range("A3&quot.Value = CableID
.Range("B3&quot.Value = CableType

Might solve your problem and cuts down on code and execution too.

Charles
Walden's Machine, Inc.
Quality Assurance/Office Developer

 

[SkipVought]

Hi,

How many row in your form? I think that I'd set a recordset object from the query and then using that recordset use the CopyFromRecordset Method to get the data into Excel (BTW, you need a Worksheet object in the application object after opening the workbook)

Set ws = Activesheet
Set rs = OpenRecordset(YourQuery, dbOpenSnapshot)
ws.Range("A3").CopyFromRecordset rs

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SpankYou]

Hey Turpis,

Thanks for the reply.
The code above does what it should do, but I need to be able to insert every record of my query, into the excel document. An example of what I need is shown below, but this doesn't work as I can't connect to a query. I know I'm confusing things, but I've never really used excel.

I suppose what I need to do (in english) is create a loop that places each record from my access query into my excel template.

test code...


Dim Count As Integer
Count = 3
Dim CnnDB As ADODB.Connection
Dim RstCableS As ADODB.Recordset
Set CnnDB = Application.CurrentProject.Connection
Set RstCableS = New ADODB.Recordset


'Open Recordset
RstCableS.Open Source:="TblCableSelection", ActiveConnection:=CnnDB, CursorType:=adOpenForwardOnly, LockType:=adLockPessimistic
RstCableS.MoveFirst
Dim xlobj As Excel.Application
Set xlobj = CreateObject("excel.application&quot



With xlobj

.Workbooks.Open Filename:="C:\WINDOWS\Desktop\CableScheduleTemplate.xlt"

Do Until RstCableS.EOF = True

.Range("A" & Count).Select
.ActiveCell.FormulaR1C1 = CableID
.Range("B" & Count).Select
.ActiveCell.FormulaR1C1 = CableType
.Range("C" & Count).Select
.ActiveCell.FormulaR1C1 = FromLocation
.Range("D" & Count).Select
.ActiveCell.FormulaR1C1 = FromUnit
.Range("E" & Count).Select
.ActiveCell.FormulaR1C1 = FromTermination
.Range("F" & Count).Select
.ActiveCell.FormulaR1C1 = FromWiringDetail
.Range("G" & Count).Select
.ActiveCell.FormulaR1C1 = ToLocation
.Range("H" & Count).Select
.ActiveCell.FormulaR1C1 = ToUnit
.Range("I" & Count).Select
.ActiveCell.FormulaR1C1 = ToTermination
.Range("J" & Count).Select
.ActiveCell.FormulaR1C1 = ToWiringDetail
.Range("K" & Count).Select
.ActiveCell.FormulaR1C1 = CableFunction

Count = Count + 1

RstCableS.MoveNext

Loop

'and so on
.Quit

End With

I'll give your code a try thanks

Thanks for any help

Sam

"You couldn't fool your mother on the foolingest day of your life if you had an electrified fooling machine." - Homer

 

[xlbo]

erm - see Skip's post.....

Rgds, Geoff
[blue]Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?[/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[SpankYou]

Hey,
yeah sorry I know I've complicated things, I've got a couple of things that I've realised I've messed up, I'll get back to you thanks for the help

Sam

"You couldn't fool your mother on the foolingest day of your life if you had an electrified fooling machine." - Homer

 

[SpankYou]

Hey,

Well I have some code that should work now but doesn't. based around what SkipVought said. The problem it comes up which is...

"The Microsoft Jet engine cannot find the input table or query", although it exists and is spelt correctly. I was wondering whether the issue may be to do with the db = CurrentDB section, as when I run the code, and check to see
if all the variables are assigned correctly, nothing seems to show for the db variable.

My code is...

Dim objXL As Excel.Application
Dim objWKB As Excel.Workbook
Dim objSHT As Excel.Worksheet
Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Const conSHEET1 = "Sheet1" 'name of your first worksheet
Const ConWKBK = "C:\WINDOWS\Desktop\CableScheduleTemplate.xlt" 'full path to your template file

Set db = CurrentDb
Set objXL = New Excel.Application
With objXL
.Visible = False
Set objWKB = .Workbooks.Open(ConWKBK)

'Populate first worksheet

'strSQL = "SELECT * FROM TblCableSelection"
Set rs = db.OpenRecordset(QryCableSelection, dbOpenSnapshot) ',
Set objSHT = objWKB.Worksheets(conSHEET1)
With objSHTt
.Range("A3&quot.CopyFromRecordset rs
End With


'set focus back to SHEET1 before saving

Set objSHT = objWKB.Worksheets(conSHEET1)
objSHT.Activate

' save the workbook

objWKB.SaveAs "C:\WINDOWS\Desktop\TempCableSchedule.xls" 'full path to saved location

End With

Set objSHT = Nothing
Set objWKB = Nothing
Set objXL = Nothing
Set rs = Nothing
Set db = Nothing

thanks for any help

Sam


"You couldn't fool your mother on the foolingest day of your life if you had an electrified fooling machine." - Homer

 

[SkipVought]

Does QryCableSelection have that SQL? ie is
QryCableSelection a string variable with the value, "SELECT * FROM TblCableSelection"?


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com