Manipulating excel with access

[jking]

Hi Everyone,

Is there a way to manipulate how access writes to excel? i mean if i have different cells for every field i have.. i've been searching the net for help on this but all i can find is where you only specify the starting row and column where the copying will start, and then it will copy the whole table unto excel from that starting point.

Any help would be greatly appreciated!

Jon

 

[georgedwilkinson]

Why not just use the TransferSpreadsheet sub (see Access help) or a pivot table (see Excel help)? You can also link to an Excel spreadsheet as a linked table. The TransferSpreadsheet method is my preferred method.

 

[pbaldy]

Sure; the term you want to search on is automation, which will let you put values anywhere you want.

 

[boblarson]

Check out this sample here. I believe there is an example of what you are looking for included in the sample:
http://www.utteraccess.com/forums/s...72619&page=0&view=collapsed&sb=5&o=93&fpart=1

 

[jking]

thank you so much guys for your replies, ill look into your suggestions, and hope it can solve my problem.

Thanks!
Jon

 

[jking]

addition to my post earlier, what i basically need is a way to control where access put the data on my excel sheet, example i want put field1 to range(A1), field to to Range(B6), field3 to range(d9) and so on.. most of the examples i see on the internet only gives the starting range and copies all data from that range onwards in a table like manner.. im sorry im really new to access

 

[boblarson]

Here's a starting point:

Dim objXL As Object
Dim xlWB As Object
Dim xlWS As Object

Set objXL = CreateObject("Excel.Application")
objXL.Visible = True

Set xlWB = objXL.Workbooks.Open("YourPathToYourExcelFile")

Set xlWS = xlWB.Worksheets("YourWorkSheetNameHere")

With xlWS
   .Range("A1").Value = Me!Field1
   .Range("B6").Value = Me!Field2
   .Range("D9").Value = Me!Field3
End With

 

[jking]

Wow!! just what i needed !!!

thank you so much Bob!!

 

[pbaldy]

Yes, thanks Bob!

 

[boblarson]

Yes, thanks Bob!

 

[jking]

Hi bob, i have a follow up question, i've now come to the data on my subform, which includes several records , is there a way i can loop thru each record and transfer the data on my excel sheet? i tried using the recordset approach but i have to control on which fields i would like copied only, it copies the whole table. I hope you can help me again on this one.

regards,
Jon

 

[boblarson]

How do you know what fields go where?

 

[jking]

hi bob,

i think the best way i can explain my situation is by example:

on my excel sheet, i have this columns

TIN NO. | BDAY | NAME | EMPLOYEE CONT. | EMPLOYER CONT | TOTAL |

my table includes all those fields plus a lot more, and contains many records.

now, how do i copy all the records on my excel sheet but with the relevant columns only? i tried doing the recordset approach in the link you gave me earlier (http://www.utteraccess.com/forums/sh...5&o=93&fpart=1) but it copies all the fields in my table to the excel sheet.

thanks and regards,
Jon

 

[Mike375]

Here is a little DB I got from this forum.

 

[jking]

Hi Mike375,

Thanks for your reply and the link, i was able to do that with Bob's code, my problem now is copying all records of a table to excel but with selected fields only.

regards,
Jon

 

[nIGHTmAYOR]

if you are planning on inserting a huge amount of manipulated data and columns in your excel sheet then i suggest u use linked table manager and deal with ur work sheet like a table u append queries to.

the way you handle where goes where is easily achieved by introducing a new column as a serial column u can use later as a key or referance in your select statments.

the above mentioned technique might be helpfull with summaries and record by record entries (which can be looped so that you append a whole query record by record but hey why the bother).

 

[jking]

hi nIGHTmAYOR,

i wont be using huge amount of data here, just a few records, 20-30 max.. I know it would be easier to just create a report in ms access for this, but the situation really calls for an excel report..

I made a do while loop to go through my table one record at a time but i cant seem to make the pointer skip to the next record, below is my code and its stuck on the first record only..

Private Sub cmdYahoo__Click()
Dim objXL As Object
Dim xlWB As Object
Dim xlWS As Object
Dim strSQL As String
Dim rst As Recordset
Dim sCriteria As String
Dim db As Database

Set objXL = CreateObject("Excel.Application")
objXL.Visible = True

Set xlWB = objXL.Workbooks.Open("c:\system\FORM.xlt")

Set xlWS = xlWB.Worksheets("Sheet1")

With xlWS
.Range("I5").Value = pMonth
.Range("J5").Value = pYear
.Range("A7").Value = Me!cName
.Range("A9").Value = Me!cAddress
.Range("F9").Value = cTinNo
.Range("H9").Value = cZipCode
End With

xnum = 12

X = ("A" & CStr(xnum))
Y = ("B" & CStr(xnum))
Z = ("C" & CStr(xnum))
A = ("H" & CStr(xnum))
B = ("I" & CStr(xnum))
C = ("J" & CStr(xnum))

If rst.RecordCount > 0 Then
rst.MoveFirst
Do While Not rst.EOF
With xlWS
.Range(X).Value = eTinNo
.Range(Y).Value = eBDay
.Range(Z).Value = eName
.Range(A).Value = eAmount
.Range(B).Value = cAmount
.Range(C).Value = tAmount
End With
xnum = xnum + 1
X = ("A" & CStr(xnum))
Y = ("B" & CStr(xnum))
Z = ("C" & CStr(xnum))
A = ("H" & CStr(xnum))
B = ("I" & CStr(xnum))
C = ("J" & CStr(xnum))

rst.MoveNext
Loop
End If
End Sub

Any help?

Regards,
Jon

 

[jking]

Guys,

I've found the error on my code

on this part:

.Range(X).Value = eTinNo
.Range(Y).Value = eBDay
.Range(Z).Value = eName
.Range(A).Value = eAmount
.Range(B).Value = cAmount
.Range(C).Value = tAmount

should be:
.Range(X).Value = rst("eTinNo")
.Range(Y).Value = rst("eBDay")
.Range(Z).Value = rst("eName")
.Range(A).Value = rst("eAmount")
.Range(B).Value = rst("cAmount")
.Range(C).Value = rst("tAmount")

regards

Jon

 

[nIGHTmAYOR]

I hate to see a code that was just copied/pasted at no serious effort at optimizing it , yet her goes how i think it should go

Private Sub cmdYahoo__Click()
Dim objXL As Object
Dim xlWB As Object
Dim xlWS As Object
Dim rst As Recordset
 
Set objXL = CreateObject("Excel.Application")
objXL.Visible = True
Set xlWB = objXL.Workbooks.Open("c:\system\FORM.xlt")
Set xlWS = xlWB.Worksheets("Sheet1")
 
With xlWS
.Range("I5").Value = pMonth
.Range("J5").Value = pYear
.Range("A7").Value = Me!cName
.Range("A9").Value = Me!cAddress
.Range("F9").Value = cTinNo
.Range("H9").Value = cZipCode
End With
 
xnum = 12
 
If rst.RecordCount > 0 Then
rst.MoveFirst
 
Do While Not rst.EOF
 
X = "A" & xnum
Y = "B" & xnum
Z = "C" & xnum
A = "H" & xnum
B = "I" & xnum
C = "J" & xnum
With xlWS
.Range(X).Value = rst("eTinNo")
.Range(Y).Value = rst("eBDay")
.Range(Z).Value = rst("eName")
.Range(A).Value = rst("eAmount")
.Range(B).Value = rst("cAmount")
.Range(C).Value = rst("tAmount")
End With
xnum = xnum + 1
rst.MoveNext
 
Loop
End If
End Sub

now the code is optimized comes my question.
Where exactly the recordset gets filled ?? I'm guessing you just croped the code to a minimal.
Best regards.