Controlling Excel From Access

[DevastatioN]

Hello,

I have a database which has a VBA procedure that takes lots of data from the tables, opens Excel up in the background, uses a template to paste in the data into the correct places on the Excel sheet.

Unfortunately, because of one to many relationships, rows have to be added in certain places because of varying data. I can add a new row in Excel from my Access VBA procedure, but the problem is it won't be formatted correctly.

I can't seem to find a way to copy a row and paste a row in Excel from Access VBA. Or even a way to format the cells using Access VBA.

Currently I'm stuck to putting 50 formatted rows in the Excel file, counting how many I use and delete the rest (if I use 7, my Access VBA procedure deleted 43 rows and then continues).

Is there anyway to for Access VBA to be able to use ALL Excel VBA stuff? I know inside of Excel this is easily possible with things like .copy and SelectionCopy.Paste, however VBA from Access doesn't recognize this. Even if Access VBA can make borders and backcolor changes etc. that'd work, but easiest is clearly just copy row + formatting and then paste copy.

Any help would be appreciated thanks.

 

[boblarson]

however VBA from Access doesn't recognize this.

Actually, it does but you have to use the correct objects to do it. What code do you currently use? We'll see if we can help you adjust it.

 

[georgedwilkinson]

Use TransferSpreadsheet and Excel automation objects.

Here's what I do:
1. write a query in Access that gives the exact dataset required in Excel (much easier to do in SQL than VBA)
2. Use TransferSpreadsheet with the source being query from #1
3. Use Excel automation objects to set fonts, shading, format, etc. in the new Excel worksheet
4. Send a bill to the customer and take the rest of the night off, cause there's really nothing left to do.

In some cases, you may have to delete Excel objects (use Kill()) or otherwise manage the files themselves.

Also, TransferSpreadsheet doesn't work well with text data over 255 bytes, in that case you must pull the data from a table with a memo field (usually as a result of aggregating smaller text fields). I just create a temporary table with a memo field and fill it with my query from #1, above, since you cannot filter the table using TransferSpreadsheet and still get more than 255 bytes to transfer.

Like Bob said, post your stuff and we'll take a look.

 

[DevastatioN]

Er, because of what I need, I will definitely keep the Excel template.

As for using the correct objects, are their any examples that use code for formating Excel etc, and/or SelectionCopy.Paste?

I will paste some of the code when I'm back at work this Friday, it's not optimal and I havent really put error checking into it yet, so the code is sloppy until I get everything working correctly.

But still, if you have any examples that'd be great, thanks.

 

[boblarson]

Okay, here's a quick sample for you.

 

[DevastatioN]

Thx Bob, I will take a closer look at that example and try to utilize it.

I am uploading a txt contaiing the code for my procedure.

However, the most important part I wish to change is this:

'Input the Objectives and Targets database information into their correct Excel locations
Do Until rstObjTgt.EOF = True
With objActiveWkb.Worksheets(1)
.cells(intTarget, 1) = rstObjTgt.Fields("Objective")
.cells(intTarget, 6) = rstObjTgt.Fields("Target")
End With
intTarget = intTarget + 1
rstObjTgt.MoveNext
'Count how many rows are entered
x = x + 1
Loop


If I could change that to something like this...

'Input the Objectives and Targets database information into their correct Excel locations
Do Until rstObjTgt.EOF = True
*objActiveWkb.Worksheets(1).row(intTarget).copy
*objActiveWkb.Worksheets(1).row(intTarget+1).Paste
With objActiveWkb.Worksheets(1)
.cells(intTarget, 1) = rstObjTgt.Fields("Objective")
.cells(intTarget, 6) = rstObjTgt.Fields("Target")
End With
intTarget = intTarget + 1
rstObjTgt.MoveNext
Loop

I would be fine, cause it would copy the row and all formatting from the prior row, and then copy it to the next row (or actually, insert copied row would be best, but either way is workable).

Thanks in advance,

 

[DevastatioN]

Hey, I got it working with

appExcel.Rows(intTarget).Select
appExcel.Selection.Copy
appExcel.ActiveSheet.Paste

Thanks very much!

So I'm assuming that as long as I use appExcel.whatever I can use any VBA things from Excel itself?

 

[mmasdf]

Okay, here's a quick sample for you.

I was blown away by Bob's sample (in a good way). It took me forever just to figure out TransferSpreadsheet. Now, my question is regarding combo boxes/values tied to another table. In the example, the # value was passed for the "Ship Via" field, not the text value. How can you get the text value passed instead?

 

[boblarson]

You can reference the column of the combo box -

"[YourFieldName]='" & Me.YourComboBox.Column(1) & "'"

You have to use the extra quotes, as shown, for text and the column numbers are zero-based so column 1 actually is (0) and Column(1) is column 2.

 

[boblarson]

Oops - nevermind. I forgot what I had put in the sample. You would need to include a field that has the text of the name. The combo in the current sample has the id as the value but you would need to maybe just include a text box with the value you want.

 

[mmasdf]

I'll try this. Thanks so much!