Transferspreadsheet Method

[allenjasonbrown@gmail.com]

I have a query that I use Transferspreadsheet method to simply export it to a excel document. (row one is column headers and then row 2 is data).


WORKS GREAT.

NOW...I would like to be able to insert a TITLE "THIS IS MY NEW DOC" in row 1 of the excel document and shift everything down one row.

Is there an easy way to do this? I have 20+ queries I need to do this to so am looking for basic code.

Any help / Examples would be GREAT.

Thank you in advance!

[boblarson]

After you have exported it, you can reopen the workbook and do it using this. Save this function into a standard module (not form, report or class module):

Function InsertTitle(strTitle As String, strFileAndPath As String)
   Dim objXL As Object
   Dim xlWB As Object
 
   Set objXL = CreateObject("Excel.Application")
   objXL.Visible = True ' you can comment this out later when it is working
 
   Set xlWB = objXL.Workbooks.Open(strFileAndPath)
   xlWB.Worksheets(1).Range("A1").EntireRow.Insert
   xlWB.Worksheets(1).Range("A1").Value strTitle
 
xlWB.Save
xlWB.Close
 
objXL.UserControl = True  ' comment this out too if you aren't leaving it visible and then
' objXL.Quit   ' uncomment this line if you comment out the visible and such 
Set objXL = Nothing
 
End Function

But the workbook would need to not be open from the TransferSpreadsheet for this.

[allenjasonbrown@gmail.com]

Thank you, I think this is exactly what I need.

I am getting an error on " xlWB.Worksheets(1).Range("A1").EntireRow.Insert"
error = "Object doesn't support this property or method"

Do I need a specific reference?

[boblarson]

Hmm, I put it into my Access database and got the error on the next line, not that one. I forgot to put the = sign between .Value and strTitle.

So what works for me is:

Function InsertTitle(strTitle As String, strFileAndPath As String)
Dim objXL As Object
Dim xlWB As Object
 
Set objXL = CreateObject("Excel.Application")
objXL.Visible = True ' you can comment this out later when it is working
 
Set xlWB = objXL.Workbooks.Open(strFileAndPath)
xlWB.worksheets(1).range("A1").EntireRow.Insert
xlWB.worksheets(1).range("A1").select
xlWB.worksheets(1).range("A1").Value = strTitle
 
xlWB.Save
xlWB.Close
 
objXL.UserControl = True ' comment this out too if you aren't leaving it visible and then
' objXL.Quit ' uncomment this line if you comment out the visible and such
Set objXL = Nothing
 
End Function

[allenjasonbrown@gmail.com]

Thank you....that worked great (sorry I had pasted the wrong line into my post...it was the "=" sign"
I really appreciate your help.

Are there other things I can do within this code? (i.e. make the title line bold, underline, etc). Is there a place I can look up different options?

Thank you again!!!

[boblarson]

Basically, do what I do. Record an Excel macro while doing what you want and then go to the VBA window in Excel and then use that code (with slight modifications).

[allenjasonbrown@gmail.com]

Thank you again. This seems to be working great and I was able to record some macros and incorporate them into the VBA code.

I did run into a couple that I had trouble with. Do you have any idea how to put these in the proper format for the above VBA? (I can do the selection part but the "= xl****" does not seem to work.

1.Range("A5:W5").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.14996795556505
.PatternTintAndShade = 0
End With

[boblarson]

If you have the EXCEL code using Late Binding, then you will have to provide the values at the top of your code (create your own constants):

Const xlSolid As Integer = 1
Const xlAutomatic As Integer = -4105

And then with this one:
Const xlThemeColorDark1 As Integer = ??? ' I don't know what it is because I don't have Access 2007/2010 handy to check. But you just open up Excel and then to TOOLS > MACROS > VISUAL BASIC (or in 2007/2010 using the Design tab) you then can type

?xlThemeColorDark1

into the Immediate Window and then hit enter and it will give you the value.

[allenjasonbrown@gmail.com]

I am using this code:

xlWB.worksheets(1).Range("A5:U5").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark2
.TintAndShade = -9.99481185338908E-02
.PatternTintAndShade = 0
End With

and have placed this at the top:
Const xlSolid As Integer = 1
Const xlAutomatic As Integer = -4105
Const xlThemeColorDark2 As Integer = 3

I am still getting a object required error.

[boblarson]

This code here isn't tied to an instantiated object:

With Selection.Interior

It should be connected to your application object. Also, you might double check to see if you now have extra EXEL.EXE process running in your task manager. There is a good possibility that there is as they will appear if you have code not tied to an instantiated object, and then only close when the database itself is closed.

Read this for more about that:
http://www.btabdevelopment.com/ts/excelinstance

[allenjasonbrown@gmail.com]

Thank you .... and yes I did have a EXCEL.EXE process running. Can you show me an example of how to use this code within the example above?
xlWB.worksheets(1).Range("A5:U5").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark2
.TintAndShade = -9.99481185338908E-02
.PatternTintAndShade = 0
End With

I cannot figure out how to tie it to an object and get it to work correctly.

Thanks.

[boblarson]

If objXL is the application object, then it would be:

With objXL.Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark2
.TintAndShade = -9.99481185338908E-02
.PatternTintAndShade = 0
End With