Formatting Excel export out of Access (1 Viewer)

[Summer123]

wow you're genius!!! thank you....
ok so i i tried copying ur modifed code and
first i get a compile error "sub or function not defined" then i click ok and it highlights "Set xlRange = Range("A2", "L18")" i see that xlsheet is not there so i put this in "xlSheet.Range" which gets rid of the error and
it opens the spreadsheet but doesnt format it
then when i close the spreadsheet it returns me to access and gives the following error "Run-time error '1004' Application-defined or object-defined error" and highlights the following code line
lngLastRow = xlSheet.Range("A65536").End(xlUp).Row
so it seems like its stoping right after it renames the sheet tab in excel. What i dont understand is I've defined lngLastRow at the top as Long, so it is defined... does it not recognize it??

if i comment this line out then it proceeds to the next lines but gets hung on "xlRange.HorizontalAlignment = xlCenter" and gives the error of "access unable to set the horizontalalignment property of the range class" so i try -4108 instead of xlcenter, still doesnt like it.. if i comment this out then it gets stuck on the next line...

am i missing something? we have everything defined correctly then why doesnt it like any of the code after we have renamed the spreadsheet?

 

[Summer123]

nevermind, i figured it out...i had to reference the Microsoft Excel under tools> reference menu... wow i am getting good at this...lol...

again thank you sooo much Mr.B!!! your help much appreciated!!!

 

[Summer123]

sorry one more question, if i had 2 queries that i wanted to export out to two tabs, i know i can do 2 transferspreadasheet commans however how would i format the second tab?

reason why i am asking is because until now i was trying to learn how to format the excel, but i have more complex project where i may have about 60 queries that i want to export to excel in different tabs individually and format each tab. Can this be done? is it simple programing or some sort of a loop that i'd have to create?

 

[Mr. B]

You would simple use code set focus to the second Worksheet and do any formating to that sheet.

I have had a serious problem to occur with my work computer and therefore I will be off line for a while. I will check back with you when I am able to get my computer back up and running.

 

[Summer123]

can you gte me started on how to get this done? have not used setfocus before and not sure where to begin...PLEASE??? thanks!

 

[Jared]

Summer -

I have gotten a lot of help from this forum just by reading, so...it's time to give back!

I happen to have cobbled together an answer for your problem yesterday, based on code from a number of sources on these forums (notably BobLarson and Jacob Mathai), plus a little bit of my own research.

The following code should let you format a succession of sheets in an Excel file, using the same set of formatting, no matter how many sheets there are:

Set objXL = CreateObject("Excel.Application")
Set xlWB = objXL.WorkBooks.Open(FILE LOCATION)

Dim intCountofSheets, intCurrentSheet As Integer
intCountofSheets = xlWB.Sheets.Count
intCurrentSheet = 1

Do While intCurrentSheet <= intCountofSheets
xlWB.WorkSheets(intCurrentSheet).Activate
With objXL.ActiveSheet
.Columns.Select
.Columns.EntireColumn.Autofit
.Range("A1").Select 'Selects first cell to deselect the rest of the sheet.
End With
intCurrentSheet = intCurrentSheet + 1
Loop

xlWB.WorkSheets(1).Activate 'Returns the selection to the first worksheet.

xlWB.Save
xlWB.Close
objXL.Quit

Set xlWB = Nothing
Set objXL = Nothing

End Sub

I was just using the code to autofit the columns; you can substitute any formatting you'd like in that section of the code, of course.

I hope this helps!

 

[Summer123]

Hello Jared,
I am glad this forum helped you, trust me a lot of the credits goes to Mr.B and also other posts i have been researching...just a mini part on my end...thank you for your code above, however i do have a question...not sure if you remember but i ad 2 functions where i called the path of the excel and the sheet itself....so with the above code do i not call the sheet function?

Function fileIn() As String
fileIn = "C:\Workspace\SCF DB\trial\test"
End Function

Function sheetIn() As String
sheetIn = "testsheet"
End Function

Private Sub Command0_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "2- Missing_Data_on_01", fileIn, True, sheetIn
'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "missing_ln", fileIn, True, missing_ln

Call FormatExcelBasic(fileIn, sheetIn)

End Sub

and formatexcelbasic is where i assume we would put the loop code but still not understanding how to call it...if you know what i mean...do i just remove the sheetIn completely??? please help!

 

[Summer123]

nevermind stupid me i got it..thank you very much Jared... but now my question becomes how do i select just the first row??? and then do what ever it is that i need to do?

before it was xlRange...but now it gives me an error stating "Object variable or with bolck variable not set" ?? what i did was dim xlrange as object and then did the following right after the autofit...

xlRange.Font.Bold = True
xlRange.Font.Size = 10
xlRange.Font.Name = "Verdana"
xlRange.HorizontalAlignment = xlCenter

 

[Summer123]

can anyone please help???
if i do this then the follwoing error comes
"runtime error 438 object doesnt support this property or method"

With xlApp.ActiveSheet
.Columns.Select
.Columns.EntireColumn.AutoFit
.Range("A1").Select 'Selects first cell to deselect the rest of the sheet.
xlApp.ActiveSheet.Rows (1) 'select the first row
xlApp.ActiveSheet.Font.Bold = True
xlApp.ActiveSheet.Font.Size = 10
xlApp.ActiveSheet.Font.Name = "Verdana"
xlApp.ActiveSheet.HorizontalAlignment = xlCenter

End With

 

[Skippers]

Hi Summer123,

I'm working on a project now that is I would say exactly the same as yours - trying to export Access data into separate formatted Excel tabs. Did you get yours resolve? I wonder will you be able to help me? My VBA is worse than yours. Please advise. Thanks in advance!