×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Access to Excel export with proper formatting
6

Access to Excel export with proper formatting

Access to Excel export with proper formatting

(OP)
The below code exports information for me from Access to Excel, and this works perfectly, however, I need for the export to properly format my excel sheets before the export (bolding, column sizes,etc.).  Based on the coding below, what would I need to add to this in order for this to work?

Private Sub Command4_Click()
Dim db As DAO.Database, rs As DAO.Recordset, str1Sql As QueryDef, strCrt As String, strDt As String
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT DISTINCT field1 FROM table ORDER By field1;")
strDt = Format(Month(Date), "00") & Format(Day(Date), "00") & Format(Year(Date), "00")
rs.MoveLast
rs.MoveFirst
Do While Not rs.EOF
strCrt = rs.Fields(0)
Set str1Sql = db.CreateQueryDef("" & strCrt, "SELECT table.*  FROM table WHERE table.field1 = '" & strCrt & "';")
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "" & strCrt, "C:\file " & strCrt & ".xls", True
DoCmd.DeleteObject acQuery, "" & strCrt
rs.MoveNext
Loop
End Sub

RE: Access to Excel export with proper formatting

Access will / can not format the export information.  It is possible to write Excel VBA code to format the cells in the target spreasheet.  It is possible to write code in Ms. Access VBA to write the Excel VBA code to format the cells and to have the code exported tot he spreasheet.  It is also possible to open the spreadsheet where the information was exported and format the cells from within Ms. Access using Ms. Access VBA code.

But you cannot get Ms. Access to format the information prior to / during the export process itself.


MichaelRed


RE: Access to Excel export with proper formatting

djmousie,

If you export to an already formated but empty sheet of an excel file, all formats are kept on the sheet (except for column sizes autofit) as long as sheet name matches exported table/query name.

Assuming that rs records rarely change, create empty formated excel files with the same sheet names (all created queries are named as that name, so they are exported to the same named sheet). Before each export create a copy of that file using the filename you are about to export and then export.

ie
rs.Fields(0)="Koykoy"
empty excel file = "myKoykoy.xls"
formated sheet name "Koykoy"
copy that as "file Koykoy.xls" --> FileCopy "C:\myKoykoy.xls", "C:\file Koykoy.xls"    
Export to "C:\file Koykoy.xls"

BTW
Format(Month(Date), "00") & Format(Day(Date), "00") & Format(Year(Date), "00") = Format(Date, "mmddyy")

and do add
rs.Close
Set rs = Nothing
Set db = Nothing
outside the loop

RE: Access to Excel export with proper formatting

(OP)
The thing is, I have would have to create over 60 formatted excel sheets, with different file names since this is what I need to achieve.  I'm sure there has to be a way for VBA to say OK, we've just created all these excel sheets, dumped all the proper information into each, now lets make the column width this size, and lets bold rows this and that...but it sounds like what MichealRed is saying above, it cannot be done...?

RE: Access to Excel export with proper formatting

Didn't say it couldn't be done, DID say you can't get the export function (or transfer... ) to do it.

Also didn't say it was a trivial exercise ... regardless of HOW you approach it, as it requires working with both versions of the object hierarchies/models and the variations in the application specific functions (e.g. object models) of VBA,


MichaelRed


RE: Access to Excel export with proper formatting




Hi,

PULL the data from Access to Excel using MS Query.

Format as you wish, set the Data Range Properties to maintain formats (will adjust to the size of the querytable)

Then Copy that format to each other sheet, assuming the same format logic, and Edit the query as needed.

BTW, why 60 sheets?

Skip,

glasses Be Advised! Coeds studying ancient Egyptian plumbing, might be known as...
Pharaoh Faucet Majors tongue

RE: Access to Excel export with proper formatting

(OP)
Im bascially taking a master list of data, and what the query does is spit out a seperate sheet by vendor, and then I email each sheet to each vendor, so thats why I'm doing it that way.      I guess what im trying to get at, is that I'm trying to save time but not having to reformat each sheet.

:)

RE: Access to Excel export with proper formatting


So that leads us back to one empty formated excel sheet???
You do keep in a table all vedors' data, don't you?

I would follow Skip's way....

RE: Access to Excel export with proper formatting



You could do this with ONE SHEET and a combobox to select vendor.  You would not need ANY code at all.

1) on sheet1 select DISTINCT vendor to make a vendor list.  Name the range.

2) on sheet2...

  a) use A1 as a Data/Validation - List to select a vendor using the named range.

  b) select the data you need with a cirteria for a vendor.  Link the parameter in A1 to the criteria in Data/Get External Data/Parameters

Skip,

glasses Be Advised! Coeds studying ancient Egyptian plumbing, might be known as...
Pharaoh Faucet Majors tongue

RE: Access to Excel export with proper formatting

(OP)
Skip,

What are your thoughts on the following code...I understand what you are trying to have me do above, it's sort-of what Id like to do, but I've gotten some feedback, and the below code is close, but I am still getting a run time error 3001 "Arugments are of the wrong type, are out of acceptable range, or are in conflict with one another" and the below code is highlighted....any thoughts as to why? I'm kinda a newbie to VBA so I'm slowly working thru my problems.

rs2.Open str1Sql, CurrentProject.Connection, acForwardOnly, acLockReadOnly, adCmdText

CODE

Private Sub Command5_Click()
 Dim db As DAO.Database, rs As DAO.Recordset, str1Sql As QueryDef, strCrt As String, strDt As String
     
    Dim oApp As Excel.Application
    Dim oWs As Excel.Worksheet
    Dim oWb As Excel.Workbook
     
     
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT DISTINCT vendor FROM billbacks ORDER By vendor;")
    strDt = Format(Month(Date), "00") & Format(Day(Date), "00") & Format(Year(Date), "00")
    rs.MoveLast
    rs.MoveFirst
     
    Set oApp = CreateObject("Excel.Application")
     
     
     
    Do While Not rs.EOF
        Set oApp = CreateObject("Excel.Application")
        Set oWb = oApp.Workbooks.Add
        Set oWs = oWb.Sheets(1)
        Dim rs2 As Recordset
        Dim y As Long, x As Long
         
        strCrt = rs.Fields(0)
         
        Set str1Sql = db.CreateQueryDef("" & strCrt, "SELECT billbacks.*  FROM billbacks WHERE billbacks.vendor = '" & strCrt & "';")
         
        Set rs2 = New Recordset
        rs2.Open str1Sql, CurrentProject.Connection, acForwardOnly, acLockReadOnly, adCmdText
        y = 1
        With oWs
            Do While Not rs2.EOF
                 
                For x = 1 To rs2.Fields.Count
                    .Cells(y, x) = rs2.Fields(x)
                Next x
                 
                y = y + 1
                 
                rs2.MoveNext
            Loop
            .Cells(1, 1).EntireRow.Font = Bold
            .Cells(1, 1).EntireRow.Interior.ColorIndex = 6
            .Cells.AutoFit
            .Cells(y + 1, 10) = WorksheetFunction.Sum(Range(Cells(2, 10), Cells(y, 10)))
        End With
        oWb.SaveAs {filename}
        oWb.Close
        Set oWb = Nothing
        Kill oWb
         
        rs.MoveNext
    Loop

End Sub

RE: Access to Excel export with proper formatting



You don't need ANY code.

Skip,

glasses Be Advised! Coeds studying ancient Egyptian plumbing, might be known as...
Pharaoh Faucet Majors tongue

RE: Access to Excel export with proper formatting

(OP)
Skip, I am trying to avoid having to select each vendor since there are so many, I understand what your saying above, u would choose a vendor from the combo box and click on a command button, but I need something that will choose all vendors and create a spreadsheet for each vendor...I'm sorry if I'm being persistent, I'm just trying to better understand...

:)

RE: Access to Excel export with proper formatting



Then all you do, with some code, is progamatically write each vendor into the cell and then copy each resultset to a new workbook to send to the vendor.  That's just a few lines of code.

If you want to use the above code, I'd suggest the CopyFromRecordset method rather than MoveNext in a loop.

Skip,

glasses Be Advised! Coeds studying ancient Egyptian plumbing, might be known as...
Pharaoh Faucet Majors tongue

RE: Access to Excel export with proper formatting

Did you ever get this project working.  I recently got called back to active duty and am stuck in Iraq without most of my resources.  For the most part I am not working in my career field, or even my old army MOS, which is probably a good thing LOL.  I am trying to do something very similiar to what you were trying to accomplish.  But like I said, previous code I wrote and my references are all back home.

I would apprciate a look at the code if that is possible.

Thanks again for your any help.

RE: Access to Excel export with proper formatting

(OP)
Unfortunetly, I gave up on the project a while back due to other priorities.  Like I said, the original code will spit items into excel from access, but WITHOUT the proper formatting.  I've been to many other forums to get other opinions.  Some have tried to help, but nothing quite worked the way I wanted to.  Most people are telling me I shouldnt have to code anything, but I have a lot of data that I need to export to MANY excel files, so if there was a way to do it, it would make my life easier.  Do a GOOGLE search with the something similar to the subject heading of this thread and also inlcude the word "Forum".  Let me know if that helps, and if you, yourself finds an answer, please let me know as well.

Thanks

RE: Access to Excel export with proper formatting

Hello djmousis,

You just about had it right all the way around.  It only took a little bit of testing and some very minor changes to get it right.  So, now you should be able to export data directly to the excel sheet and also multiple sheets.  Just drop this code behind a button and away you go.
          ************************************
    Dim db As DAO.Database, rs As DAO.Recordset, str1Sql As QueryDef, strCrt As String, strDt As String
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT distinct fieldname FROM table (or query Probably without parameters) ORDER By fieldname;")
        '**** fieldname, consider this to be how the records are grouped within the dataset.
    
    rs.MoveLast
    rs.MoveFirst
    
    Do While Not rs.EOF
        strCrt = rs.Fields(0)
        Set str1Sql = db.CreateQueryDef("" & strCrt, "SELECT table.*  FROM table WHERE table.fieldname = '" & strCrt & "';")
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "" & strCrt, "C:\filename " & ".xls", True
        DoCmd.DeleteObject acQuery, "" & strCrt
    rs.MoveNext
    Loop
    
    rs.Close
    Set rs = Nothing
    Set db = Nothing

            ********************************

Thank you again for all your help.  

Peace Paul

RE: Access to Excel export with proper formatting

(OP)
Yah I was able to get that to work, it's properly formatting the excel sheet (custum headers) and column widths and what not that I was also trying to add to the code.  Since I have about 60 spreadsheets of data that exports, they all export unformatted.  See what IM saying?

RE: Access to Excel export with proper formatting

I am thinking that each worksheet is basically identical?

If they are, can you create a "Master" worksheet in a separate workbook, with all the formating complete.

1.  You could consider calling a macro that would then select and copy the entire working MASTER worksheet.

2.  Have the macro select the 1st worksheet and do a paste special formating only.

3. Itenerate through each worksheet and do the same thing.

Haven't done that kind of work in a long time.  Just hoping to give you another approach to the issue.

Good luck.

RE: Access to Excel export with proper formatting

(OP)
Well the problem with using a Macro with a master sheet is, that I have about 60 seperate workbooks that spit out once the query is finished.  I'd have to open up each sheet individually to reformat, that is what I am trying to avoid.

Thanks again with the follow up and come home safe.

RE: Access to Excel export with proper formatting

I agree you should not be opening the workbook.  Just itenerate through each worksheet programmatically, then have the program do the paste special.

so..

Workbook a is the master.

your program spits out the 60 worksheets into a new workbook, b.

1.  call a procedure that opens to the master worksheet and do a copy of the entire worksheet.

2.  have the procedure then move to workbook b worksheet(x), select the entire worksheet and then do a paste special, formating.

3.  have the procedure then move to the next worksheet and repeat 2 until there are no more worksheets.  

Anyway, your post was so helpful, that I wanted to offer some ideas.  I have done what I am trying to describe here, but I really don't have access to my reference materials, or previous code, at least for a few more months.  If a master worksheet is not practical, then of course this idea won't work.

Good Luck.

RE: Access to Excel export with proper formatting

Ok sounds like this has been around a little while so i'm going to put my 2 cents in. I had to do something similar to this and what I ended up doing is to add the excel object to the routine and then a few lines to format the columns as needed then delete the query def as in your example before the rs. next statement. worked for my needs (multiple sheets as well as multiple workbooks) i'll dig through my code to see if i can find the routine i used if you would like to see it. my code is longer than what your needing i think as i have 40 columns i'm dealing with all with different widths and formatting.

RE: Access to Excel export with proper formatting

(OP)
that would be much appreciated!

Thanks

RE: Access to Excel export with proper formatting

My solution was to do something like this.

CODE

Function MakeSS_ALEADER()
    ' Declare an object variable to hold the object
    ' reference. Dim as Object causes late binding.
    DoCmd.Hourglass True
    DoCmd.OpenForm "frmWorking"
    DoCmd.RepaintObject acForm, "frmWorking"
    Dim ExcelSheet As Object, XL As Object
    
    Dim DB
    Dim RS
    
    Dim X
    
    X = 0
    
    Set DB = CurrentDb()
    Set RS = DB.OpenRecordset("Select * from qrySelectReqReportOpen_ALL ORDER BY [Area Leader], [Req#]", dbOpenDynaset, dbReadOnly)
    Set XL = CreateObject("Excel.Application")
    'Set ExcelSheet = CreateObject("Excel.Sheet")
    Set ExcelSheet = XL.Workbooks.Add("C:\templates\aleader.XLT")

    'open up template
    'ExcelSheet.Application.Workbooks.Open "C:\AL.XLT"

    Do Until RS.EOF
        X = X + 1
        'populate excel row
        ExcelSheet.Sheets(1).Cells((X + 1), 1).Value = X
        ExcelSheet.Sheets(1).Cells((X + 1), 2).Value = RS("Req#")
        ExcelSheet.Sheets(1).Cells((X + 1), 3).Value = RS("opened")
        ExcelSheet.Sheets(1).Cells((X + 1), 4).Value = RS("targethiredate")
        ExcelSheet.Sheets(1).Cells((X + 1), 5).Value = RS("Status")
        ExcelSheet.Sheets(1).Cells((X + 1), 6).Value = RS("Position")
        ExcelSheet.Sheets(1).Cells((X + 1), 7).Value = RS("Hiring Manager")
        ExcelSheet.Sheets(1).Cells((X + 1), 8).Value = RS("Area Leader")
        ExcelSheet.Sheets(1).Cells((X + 1), 9).Value = RS("Cost Center")
        ExcelSheet.Sheets(1).Cells((X + 1), 10).Value = RS("Department")
        ExcelSheet.Sheets(1).Cells((X + 1), 11).Value = RS("Recruitor1")
        ExcelSheet.Sheets(1).Cells((X + 1), 12).Value = RS("Location")
                
        'Move to next record
        RS.MoveNext
    Loop
    ' Save the sheet to C:\ directory.
    ExcelSheet.Application.DisplayAlerts = False
    ExcelSheet.SaveAs "C:\ALEADER.XLS"
    ExcelSheet.Application.DisplayAlerts = True
    'ExcelSheet.Application.View
    On Error Resume Next
    ' Close Excel with the Quit method on the Application object.
    ExcelSheet.Application.Quit
    RS.Close
    DB.Close
    ' Release the object variable.
    Set ExcelSheet = Nothing
    Set RS = Nothing
    Set DB = Nothing
    DoCmd.Close acForm, "frmWorking"
    DoCmd.Hourglass False
    MsgBox "Area Leader created on C:\ALEADER.XLS"
    X = Shell("excel.exe c:\aleader.xls", vbMaximizedFocus)
End Function

RE: Access to Excel export with proper formatting

5
I do this fairly often.  I create the query and export it to excel.  You than open the excel sheet, select, 'Tools/Macros/Record New Macro' and make your formatting changes.  You end the recording, hit ALT-F11 to get into code view and view the VBA generated in module1 in the 'modules' section.  This will give you the starting point to recreating the code in Access.  For simple formatting - i.e., resize columns, bold headers, freeze panes, you can have a simple sub that does the work for you and call it.

CODE

Public Sub FormatExcelBasic(fileIn As String, sheetIn As String)

'you must add a reference to excel in your project for this to work
'i.e. Microsoft Excel 11.0 Object Library

'fileIn is the fullpath and name of the excel file

'sheetIn is the name of the worksheet you are trying to format - typically this
'is the first 31 characters of the query you exported with the
'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,... command in a different sub

'if you run this code against any excel spreadsheet: FormatExcelBasic "c:\test.xls", "testsheet"
'the test sheets will end up with autofitted columns and frozen, bolded, aqua headers

On Error GoTo errHan
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlRange As Excel.Range
Dim lngLastRow As Long
Dim strCell As String

Set xlApp = New Excel.Application
xlApp.Visible = False

Set xlBook = xlApp.Workbooks.Open(fileIn)

Set xlSheet = xlBook.Sheets(sheetIn)

'rename the sheet to something more friendly to humans
xlBook.Sheets(sheetIn).NAME = "New Sheet Name"

'this will give you the last row used on the spreadsheet
'in case you want to programatically create a total line - there must
'be something in column A for this to work, of use another column that
'has data in every row
lngLastRow = xlSheet.Range("A65536").End(xlUp).Row

'select the first row
Set xlRange = xlSheet.Rows(1)
'bold the selection, set the font and fontsize, center all the cells
xlRange.Font.Bold = True
xlRange.Font.Size = 10
xlRange.Font.NAME = "Verdana"
xlRange.HorizontalAlignment = xlCenter

'stretch all the cells to 30 - this maks the auto work better
xlSheet.Cells.EntireColumn.ColumnWidth = 30
'autofit the columns
xlSheet.Cells.EntireColumn.AutoFit

'freeze the pane so the header row doesn't scroll
xlSheet.Activate
xlSheet.Range("A2", "A2").Select
xlApp.ActiveWindow.FreezePanes = True

'the following code is some examples of different things to do in VBA
'with the Excel object - some stuff will repeat - this is just a bunch of samples

'do some settins for the page layout when printing
With xlSheet.PageSetup
        .LeftHeader = "Left Header Here"
        .CenterHeader = "Center Header Here"
        .CenterFooter = "Page &P" 'print the page number bottom center
        .LeftMargin = xlApp.InchesToPoints(0.25)
        .RightMargin = xlApp.InchesToPoints(0.25)
        .TopMargin = xlApp.InchesToPoints(1)
        .BottomMargin = xlApp.InchesToPoints(1)
        .HeaderMargin = xlApp.InchesToPoints(0.5)
        .FooterMargin = xlApp.InchesToPoints(0.5)
        .PrintQuality = 600
        .Orientation = xlLandscape
        .PaperSize = xlPaperLegal
        .FirstPageNumber = xlAutomatic
        .Order = xlOverThenDown
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
        .PrintGridlines = True
End With

'some examples of setting formats for columns
Set xlRange = xlSheet.Columns("N:N")
xlRange.NumberFormat = "$#,##0"
Set xlRange = xlSheet.Columns("L:M")
xlRange.NumberFormat = "#,##0"
Set xlRange = xlSheet.Columns("CG:CK")
xlRange.NumberFormat = "0.0%"

'some more formating - sets the entire sheetfont, then bolds the header
Set xlRange = xlSheet.Cells.EntireRow
xlRange.Font.NAME = "Arial"
xlRange.Font.Size = 8
Set xlRange = xlSheet.Rows("1:1")
xlRange.Font.FontStyle = "Bold"
xlRange.Interior.ColorIndex = 8
xlRange.HorizontalAlignment = xlCenter

'This is how you can draw some borders around a selected range
With xlRange.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
End With
With xlRange.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
End With

With xlRange.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
End With
With xlRange.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
End With
With xlRange.Borders(xlInsideVertical)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
End With

'this is how you can set the headers on specific columns
xlSheet.Cells(1, 1).Value = "Header 1"
xlSheet.Cells(1, 2).Value = "Header 2"

'some more auofitting and freezing
xlSheet.Cells.EntireColumn.ColumnWidth = 30
'autofit the columnms
xlSheet.Cells.EntireColumn.AutoFit
'lock the first row
xlSheet.Activate
xlSheet.Range("A2", "A2").Select
xlApp.ActiveWindow.FreezePanes = True

'need to save all the changes and release all the variables
'*****
'IF YOU DO NOT RELEASE ALL THE VARIABLE IN THE CORRECT ORDER, YOU WILL
'END UP WITH INVISBLE EXCEL SESSIONS THAT NEVER CLOSE
'the invisible sessions can be killed from task manager, but you
'should try to always get the excel object killed before exiting the sub

xlBook.Save
Set xlRange = Nothing
Set xlSheet = Nothing
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing

Exit Sub
errHan:
    MsgBox Err.Number & " - " & Err.Description & vbCrLf & vbCrLf & _
    "Error occurred during FormatExcelBasic function.", vbCritical, "Error!"

'if there was an error, need to save whatever changes we made and clear the
'variables
'IF YOU DO NOT RELEASE ALL THE VARIABLE IN THE CORRECT ORDER, YOU WILL
'END UP WITH INVISBLE EXCEL SESSIONS THAT NEVER CLOSE
'the invisible sessions can be killed from task manager, but you
'should try to always get the excel object killed before exiting the sub
   On Error Resume Next
   xlBook.Save
   Set xlRange = Nothing
   Set xlSheet = Nothing
   Set xlBook = Nothing
   xlApp.Quit
   Set xlApp = Nothing
   Exit Sub
   
   Resume
End Sub

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close