Formatting Excel export out of Access

[Summer123]

Hello, I am new to VBA and I have simple form with a button, which i have used to export out a query to excel spreadsheet. It works perfectly however when i open the spreadsheet it is not formatted correctly. my question is, is there a way to format the excel spreadsheet? I've seen threads on this however I am not understanding the codes...I am very new at VBA and it seems like folks are asking to do excel automation??? no idea what it is and where to start in order to code this into my form... any help would be appreciated.. below is my code...

Function FN() As String
FN = "C:\Workspace\SCF DB\trial\test"
End Function
Function SN() As String
SN = "testsheet"
End Function
Private Sub Command0_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "2- Missing_Data_on_01", FN, True, SN
End Sub

 

[Summer123]

can someone please assist with my question??? i am very new to this and hope anyone can assist with my issue

 

[Mr. B]

You are correct in that you will need to use VBA code to first open the spreadsheet after you have exported the data to it and then literally apply the desired formatting to it and then save it with the formatting. All of this can be done using VBA code from within Access.

One other method that I have used in the past, depending on the amount of data that is being moved to the spreadsheet, is to create a "template" type spreadsheet, pre-formatted with the desired formatting. The use VBA code to place the data in the appropriate locations in the spreadsheet. This still involves the use of VBA code and in this case you would be opening a recordset in Access using your query that you already have and then step through all of the records in that recordset, populating specific cells in the pre-formatted worksheet.

As you are already aware of, there are may posts and articles on Excel Automation from Access. You can have Excel help you with some of the code writing by recording macros to do the formatting you want to do and then use that same code (might require some modifications) in Access to accomplish the same desired results.

Here is a link that might at least get you started:
http://www.mvps.org/access/modules/mdl0006.htm

I hope that this will at least point you in a direction that will help you move forward with your project. Pleae post back when you have specific questions.

 

[Mr. B]

Here is some VBA code that I used recently to export data to spreadsheets. In this situation, I just export the data to a specified worksheet. Then I programatically move the exported data to the desired worksheet and then format it appropriately.

There are notes and comments in this code that might assist you in moving forward with your project.

On thing you will certainly have to do is set a reference to Excel from your VBA cdoe window.

The code below works in my applicaiton but it will not automatically work for you. You should only use this code for examples of how and what to do.

'******Create and export the "Orders" records
    'export the results of the "ForecastOrders_qry" query to the new Excel file
    'when the data is actully exported, a new worksheet named for the query used
    'in the export is created and the data is placed on this new worksheet
    'Code used later, moves this data to the appropriate worksheet and formats it
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "ForecastOrders_qry", strNewPathAndFileName, True
    
    'update the progress bar - 7
    Me.rectProgressBar.Width = (intTotalWidth / intProgressBarMax) * intCurrentProgress
    intCurrentProgress = intCurrentProgress + 1
    
    Me.lblUserInfo.Caption = "Creating ""SSP"" information for Fleet Forecast!"
    Me.Repaint
    DoEvents
    '******Create and export the "SSP" records
    'export the results of the "ForecastSSP_qry" query to the new Excel file
    'when the data is actully exported, a new worksheet named for the query used
    'in the export is created and the data is placed on this new worksheet
    'Code used later, moves this data to the appropriate worksheet and formats it
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "ForecastSSP_qry", strNewPathAndFileName, True
    
    'update the progress bar - 8
    Me.rectProgressBar.Width = (intTotalWidth / intProgressBarMax) * intCurrentProgress
    intCurrentProgress = intCurrentProgress + 1
    
    Me.lblUserInfo.Caption = "Creating ""Removals"" information for Fleet Forecast!"
    Me.Repaint
    DoEvents
    '******Create and export the "Forecast Removals" records
    'export the results of the "ForecastRemovals_qry" query to the new Excel file
    'when the data is actully exported, a new worksheet named for the query used
    'in the export is created and the data is placed on this new worksheet
    'Code used later, moves this data to the appropriate worksheet and formats it
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "ForecastRemovals_qry", strNewPathAndFileName, True
    DoEvents
    
    'update the progress bar - 9
    Me.rectProgressBar.Width = (intTotalWidth / intProgressBarMax) * intCurrentProgress
    intCurrentProgress = intCurrentProgress + 1
    
    '******Create and export the "Forecast Inventory" records
    'process the "Inventory" exported records here
    Me.lblUserInfo.Caption = "Creating the ""Inventory"" data to the Fleet Forecast!"
    Me.Repaint
    DoEvents
    
    'at this point, the new "tblFleetForecastInv" table has been created, and populated with data
    'remove any records where there is a zero for every reporting month
    'read the field names from the "tblFleetForecastInv" table into the "FldNames" array variable
    'Note: The month field names are created programatically above
    Set TblRs = CurrentDb.OpenRecordset("tblFleetForecastInv")
    cntr = 1
    For Each fld In TblRs.Fields
        If fld.Name <> "Zone" And fld.Name <> "Type" And fld.Name <> "Make" And fld.Name <> "Model" Then
            FldNames(cntr) = fld.Name
            'Debug.Print FldNames(cntr)
            cntr = cntr + 1
        End If
    Next fld
    'create the "Delete" type sql statement using the value in the array variable for field names
    strSql = "DELETE * FROM tblFleetForecastInv " _
           & "WHERE (((tblFleetForecastInv.[" & FldNames(1) & "]) Is Null) " _
           & "AND ((tblFleetForecastInv.[" & FldNames(2) & "]) Is Null) " _
           & "AND ((tblFleetForecastInv.[" & FldNames(3) & "]) Is Null) " _
           & "AND ((tblFleetForecastInv.[" & FldNames(4) & "]) Is Null) " _
           & "AND ((tblFleetForecastInv.[" & FldNames(5) & "]) Is Null) " _
           & "AND ((tblFleetForecastInv.[" & FldNames(6) & "]) Is Null) " _
           & "AND ((tblFleetForecastInv.[" & FldNames(7) & "]) Is Null) " _
           & "AND ((tblFleetForecastInv.[" & FldNames(8) & "]) Is Null) " _
           & "AND ((tblFleetForecastInv.[" & FldNames(9) & "]) Is Null) " _
           & "AND ((tblFleetForecastInv.[" & FldNames(10) & "]) Is Null) " _
           & "AND ((tblFleetForecastInv.[" & FldNames(11) & "]) Is Null) " _
           & "AND ((tblFleetForecastInv.[" & FldNames(12) & "]) Is Null));"
    'run the delete query
    CurrentDb.Execute strSql
    
    'Export the "Inventory" records to the Excel workbook
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryFleetForcastInvExport", strNewPathAndFileName, True
    
    'update the progress bar - 10
    Me.rectProgressBar.Width = (intTotalWidth / intProgressBarMax) * intCurrentProgress
    intCurrentProgress = intCurrentProgress + 1
    
    With Me.lblUserInfo
        .Caption = "Creating new Fleet Forecast Workbook!"
        .Visible = True
    End With
    
    'open the Excel spreadsheet with the exported data
    Set objXLApp = CreateObject("Excel.Application")
    DoEvents
    
    Set objXlBook = objXLApp.Workbooks.Open(strNewPathAndFileName)
    DoEvents
    
    'update the progress bar -11
    Me.rectProgressBar.Width = (intTotalWidth / intProgressBarMax) * intCurrentProgress
    intCurrentProgress = intCurrentProgress + 1
    
    With Me.lblUserInfo
        .Caption = "Moving the ""Orders"" data to the ""Orders"" worksheet!"
        .Visible = True
    End With
    
    'With the Workbook open:
    '***MOVE THE "ORDERS" EXPORTED DATA
    'set focus to the sheet that was created when the query was exported
    'the name of the sheet will always be the name of the query that was used to do the export
    'Sheets("ForecastOrders_qry").Select
    Set objXLSheet = objXlBook.Sheets("ForecastOrders_qry")
    DoEvents
    'find the last used cell in Column "A" (Center)
    LastRow = objXLSheet.Range("A65536").End(xlUp).Row
    'select and copy all of the data that was exported
    objXLSheet.Range("A1:P" & LastRow).Copy
    'make the "Orders" worksheet the active worksheet
    Set objXLSheet = objXlBook.Sheets("Orders")
    'paste in only the values copied from the "ForecastOrders_qry" worksheet
    objXLSheet.Range("B2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    'delete the worksheet where the data was orginally places
    objXLApp.DisplayAlerts = False
    objXlBook.Sheets("ForecastOrders_qry").Delete
    objXLApp.DisplayAlerts = True
    
    'update the progress bar -12
    Me.rectProgressBar.Width = (intTotalWidth / intProgressBarMax) * intCurrentProgress
    intCurrentProgress = intCurrentProgress + 1
    
    With Me.lblUserInfo
        .Caption = "Moving the ""SSP"" data to the ""Orders"" worksheet!"
        .Visible = True
    End With
    
    Me.Repaint
    DoEvents
    
    '***MOVE THE "SSP" EXPORTED DATA
    'set focus to the sheet that was created when the query was exported
    'the name of the sheet will always be the name of the query that was used to do the export
    'objXlBook.Sheets("ForecastSSP_qry").Select
    
    'make the "ForecastSSP_qry" worksheet to be the active worksheet
    Set objXLSheet = objXlBook.Sheets("ForecastSSP_qry")
    objXLSheet.Activate
    DoEvents
    
    'find the last used cell in Column "A" (Center)
    LastRow = objXLSheet.Range("A65536").End(xlUp).Row
    
    'select and copy all of the data that was exported
    objXLSheet.Range("A2:P" & LastRow).Copy
    
    'make the "Orders" worksheet to be the active worksheet
    Set objXLSheet = objXlBook.Sheets("Orders")
    objXLSheet.Activate
    
    'find the last used cell in Column "B" (Center)
    LastRow = objXLSheet.Range("B65536").End(xlUp).Row
    
    objXLSheet.Range("B" & LastRow + 1).Select
    
    'set the focus to the next available cell in column "B" in the "Orders" workwheet
    objXLSheet.Range("B" & LastRow + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    'delete the worksheet where the data was orginally placed
    objXLApp.DisplayAlerts = False
    objXlBook.Sheets("ForecastSSP_qry").Delete
    objXLApp.DisplayAlerts = True
    
    'When the exported Orders and SSP records have all been moved to the "Orders" worksheet
    Me.lblUserInfo.Caption = "Formatting the ""Orders"" worksheet of the Fleet Forecast workbook!"
    Me.Repaint
    DoEvents
    
    'make the "Orders" worksheet to be the active worksheet
    Set objXLSheet = objXlBook.Sheets("Orders")
    
    'find the last used cell in Column "B" (Center)
    LastRow = objXLSheet.Range("B65536").End(xlUp).Row
    
    'set the value of the "StartRow" variable to be used in a "FormulaR1C1" type formula
    StartRow = LastRow - 2
    
    'update the progress bar -13
    Me.rectProgressBar.Width = (intTotalWidth / intProgressBarMax) * intCurrentProgress
    intCurrentProgress = intCurrentProgress + 1
    
    With Me.lblUserInfo
        .Caption = "Formatting the ""Orders"" worksheet!"
        .Visible = True
    End With
    'format the column headings
    For c = 6 To 17
        'reformat the YR-Mo Colunm name
        strOrgDateVal = objXLSheet.Cells(2, c).Value
        objXLSheet.Cells(2, c).Value = FormatColName(strOrgDateVal)
    Next c
    
    'apply center formatting to all cells
    objXLSheet.Range("B3:R" & LastRow + 1).HorizontalAlignment = xlCenter
    
    'show the cell borders
    With objXLSheet.Range("B3:R" & LastRow + 1).Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    
    'update the progress bar -14
    Me.rectProgressBar.Width = (intTotalWidth / intProgressBarMax) * intCurrentProgress
    intCurrentProgress = intCurrentProgress + 1
    
    'make all blank cells to have a zero instead of just blank
    For c = 6 To 17
        For i = 3 To LastRow
            If IsEmpty(objXLSheet.Cells(i, c).Value) Then
                objXLSheet.Cells(i, c).Value = 0
            End If
        Next i
        'add the TOTALS formula below the last row
        objXLSheet.Cells(i, c).FormulaR1C1 = "=SUM(R[-" & StartRow & "]C:R[-1]C)"
    Next c
    
    'Add TOTALS text and formatting
    objXLSheet.Range("B" & LastRow + 1).Value = "TOTALS"
    
    With objXLSheet.Range("B" & LastRow + 1 & ":E" & LastRow + 1)
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .MergeCells = True
        .Font.Bold = True
        .Borders.TintAndShade = -0.14996795556505
        .Borders.LineStyle = xlContinuous
        .Borders.Weight = xlThin
    End With
    
    'Bold the range where the count totals are and make it bold
    With objXLSheet.Range("B" & LastRow + 1 & ":R" & LastRow + 1).Font
        .FontStyle = "Bold"
    End With
    
    'format the borders
    With objXLSheet.Range("B" & LastRow + 1 & ":R" & LastRow + 1).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        '.ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.14996795556505
        .PatternTintAndShade = 0
    End With
    With objXLSheet.Range("B" & LastRow + 1 & ":R" & LastRow + 1).Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    
    'set focus to "A1"
    objXLSheet.Range("A1").Select
    
    'update the progress bar -15
    Me.rectProgressBar.Width = (intTotalWidth / intProgressBarMax) * intCurrentProgress
    intCurrentProgress = intCurrentProgress + 1
    
    With Me.lblUserInfo
        .Caption = "Formatting the ""Removals"" worksheet!"
        .Visible = True
    End With
    
    Me.Repaint
    DoEvents
    
    '***MOVE AND FORMAT THE "REMOVALS" EXPORTED DATA
    'set focus to the sheet that was created when the query was exported
    'the name of the sheet will always be the name of the query that was used to do the export
    Set objXLSheet = objXlBook.Sheets("ForecastRemovals_qry")
    DoEvents
    'find the last used cell in Column "A" (Center)
    LastRow = objXLSheet.Range("A65536").End(xlUp).Row
    'select and copy all of the data that was exported
    objXLSheet.Range("A1:P" & LastRow).Copy
    'make the "Removals" worksheet the active worksheet
    Set objXLSheet = objXlBook.Sheets("Removals")
    'paste the values copied from the "ForecastOrders_qry" worksheet starting in "B2"
    objXLSheet.Range("B2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    'delete the worksheet where the data was orginally places
    objXLApp.DisplayAlerts = False
    objXlBook.Sheets("ForecastRemovals_qry").Delete
    objXLApp.DisplayAlerts = True
    'make the "Removals" worksheet the active worksheet
    Set objXLSheet = objXlBook.Sheets("Removals")
    'find the last used cell in Column "B" (Center)
    LastRow = objXLSheet.Range("B65536").End(xlUp).Row
    
    'set the value of the "StartRow" variable to be used in a "FormulaR1C1" type formula
    StartRow = LastRow - 2
    
    'Set the format of the column headings
    For c = 6 To 17
        'reformat the YR-Mo Colunm name
        strOrgDateVal = objXLSheet.Cells(2, c).Value
        objXLSheet.Cells(2, c).Value = FormatColName(strOrgDateVal)
    Next c
    'apply center formatting to all cells
    objXLSheet.Range("B3:R" & LastRow + 1).HorizontalAlignment = xlCenter
    
    With objXLSheet.Range("B3:R" & LastRow + 1).Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    
    'update the progress bar -16
    Me.rectProgressBar.Width = (intTotalWidth / intProgressBarMax) * intCurrentProgress
    intCurrentProgress = intCurrentProgress + 1
    
    'make all blank cells to have a zero instead of just blank
    For c = 6 To 17
        For i = 3 To LastRow
            If IsEmpty(objXLSheet.Cells(i, c).Value) Then
                objXLSheet.Cells(i, c).Value = 0
            End If
        Next i
        'add the TOTALS formula
        objXLSheet.Cells(i, c).FormulaR1C1 = "=SUM(R[-" & StartRow & "]C:R[-1]C)"
    Next c
    
    'Add TOTALS text and formatting
    objXLSheet.Range("B" & LastRow + 1).Value = "TOTALS"
    
    With objXLSheet.Range("B" & LastRow + 1 & ":E" & LastRow + 1)
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .MergeCells = True
        .Font.Bold = True
        .Borders.TintAndShade = -0.14996795556505
        .Borders.LineStyle = xlContinuous
        .Borders.Weight = xlThin
    End With
    'select the range where the count totals are and make it bold
    With objXLSheet.Range("B" & LastRow + 1 & ":R" & LastRow + 1).Font
        .FontStyle = "Bold"
    End With
    With objXLSheet.Range("B" & LastRow + 1 & ":R" & LastRow + 1).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        '.ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.14996795556505
        .PatternTintAndShade = 0
    End With
    With objXLSheet.Range("B" & LastRow + 1 & ":R" & LastRow + 1).Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    'make the "Removals" worksheet the active worksheet
    Set objXLSheet = objXlBook.Sheets("Removals")
    objXLSheet.Activate
    'set focus to "A1"
    objXLSheet.Range("A1").Select
    
    'update the progress bar -17
    Me.rectProgressBar.Width = (intTotalWidth / intProgressBarMax) * intCurrentProgress
    intCurrentProgress = intCurrentProgress + 1
    
    'process the "Inventory" exported records here
    With Me.lblUserInfo
        .Caption = "Moving the ""Inventory"" data to the ""Inventory"" worksheet!"
        .Visible = True
    End With
    Me.Repaint
    DoEvents
    
    'With the Workbook open:
    '***MOVE THE "INVENTORY" EXPORTED DATA
    'set focus to the sheet that was created when the query was exported
    'the name of the sheet will always be the name of the query or table that was used to do the export
    'make the "qryFleetForcastInvExport" worksheet as the active worksheet
    Set objXLSheet = objXlBook.Sheets("qryFleetForcastInvExport")
    DoEvents
    'find the last used cell in Column "A" (Center)
    LastRow = objXLSheet.Range("A65536").End(xlUp).Row
    'select and copy all of the data that was exported
    objXLSheet.Range("A1:P" & LastRow).Copy
    'make the "Inventory" worksheet the active worksheet
    Set objXLSheet = objXlBook.Sheets("Inventory")
    'paste in only the values copied from the "ForecastOrders_qry" worksheet
    objXLSheet.Range("B2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    'delete the worksheet where the data was orginally places
    objXLApp.DisplayAlerts = False
    objXlBook.Sheets("qryFleetForcastInvExport").Delete
    objXLApp.DisplayAlerts = True
    
    'When the exported Inventory records have all been moved to the "Inventory" worksheet
    'format the Inventory Worksheet
    
    'update the progress bar -18
    Me.rectProgressBar.Width = (intTotalWidth / intProgressBarMax) * intCurrentProgress
    intCurrentProgress = intCurrentProgress + 1
    
    With Me.lblUserInfo
        .Caption = "Formatting the ""Inventory"" worksheet!"
        .Visible = True
    End With
    
    'make the "Inventory" worksheet as the active worksheet
    Set objXLSheet = objXlBook.Sheets("Inventory")
    
    'find the last used cell in Column "B" (Center)
    LastRow = objXLSheet.Range("B65536").End(xlUp).Row
    'set the value of the "StartRow" variable to be used in a "FormulaR1C1" type formula
    StartRow = LastRow - 2
    
    'change the format of the column headers
    For c = 6 To 17
        'reformat the YR-Mo Colunm name
        strOrgDateVal = objXLSheet.Cells(2, c).Value
        objXLSheet.Cells(2, c).Value = FormatColName(strOrgDateVal)
    Next c
    objXLSheet.Range("B3:Q" & LastRow + 1).HorizontalAlignment = xlCenter
    With objXLSheet.Range("B3:Q" & LastRow + 1).Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    
    'update the progress bar -19
    Me.rectProgressBar.Width = (intTotalWidth / intProgressBarMax) * intCurrentProgress
    intCurrentProgress = intCurrentProgress + 1
    
    'make all blank cells to have a zero instead of just blank
    For c = 6 To 17
        For i = 3 To LastRow
            If IsEmpty(objXLSheet.Cells(i, c).Value) Then
                objXLSheet.Cells(i, c).Value = 0
            End If
        Next i
        'add the TOTALS formula
        objXLSheet.Cells(i, c).FormulaR1C1 = "=SUM(R[-" & StartRow & "]C:R[-1]C)"
    Next c
    
    'Add TOTALS text and formatting
    objXLSheet.Range("B" & LastRow + 1).Value = "TOTALS"
    With objXLSheet.Range("B" & LastRow + 1 & ":E" & LastRow + 1)
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .MergeCells = True
        .Font.Bold = True
        .Borders.TintAndShade = -0.14996795556505
        .Borders.LineStyle = xlContinuous
        .Borders.Weight = xlThin
    End With
    'select the range where the count totals are and make it bold
    With objXLSheet.Range("B" & LastRow + 1 & ":Q" & LastRow + 1).Font
        .FontStyle = "Bold"
    End With
    'format the borders
    With objXLSheet.Range("B" & LastRow + 1 & ":Q" & LastRow + 1).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        '.ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.14996795556505
        .PatternTintAndShade = 0
    End With
    With objXLSheet.Range("B" & LastRow + 1 & ":Q" & LastRow + 1).Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    'set focus to "A1"
    'make the "Inventory" worksheet as the active worksheet
    Set objXLSheet = objXlBook.Sheets("Inventory")
    objXLSheet.Activate
    objXLSheet.Range("A1").Select
    'when the processing is finished
    ''make the "Orders" worksheet as the active worksheet
    Set objXLSheet = objXlBook.Sheets("Orders")
    
    'update the progress bar -20
    Me.rectProgressBar.Width = (intTotalWidth / intProgressBarMax) * intCurrentProgress
    intCurrentProgress = intCurrentProgress + 1
    
    With Me.lblUserInfo
        .Caption = "Saving Fleet Forecast workbook!"
        .Visible = True
    End With
    Me.Repaint
    DoEvents
    
    'save the changes
    objXlBook.Save

I hope this helps you out.

 

[Summer123]

Thank you Mr. B! I will go ahead and work on this and get back to you if I have any question... I really appreciate ur assistance!!!

 

[Summer123]

Hello Mr.B, one quick question when you say "On thing you will certainly have to do is set a reference to Excel from your VBA cdoe window. " can you please advise as to what you mean? Again i am very new at this and dont know much about VBA, learning as i go though...

 

[Summer123]

ok so this is what i have so far, there are no errors but it does not format the spreadsheet either...whats wrong here???? i have created a module for formatting and called it in the comand button code... am i doing something wrong here?


my module
Option Compare Database
Public Sub FormatExcelBasic(fileIn As String, sheetIn As String)
On Error GoTo errHan
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim xlRange As Object
Dim lngLastRow As Long
Dim strCell As String

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(fileIn)
Set xlSheet = xlBook.Sheets(sheetIn)
xlBook.Sheets(sheetIn).Name = "New Sheet Name"
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
'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
'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
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 ModifyExportedExcelFileFormats function.", vbCritical, "Error!"

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


my form
Option Compare Database
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
Call FormatExcelBasic(fileIn, sheetIn)
End Sub

 

[Mr. B]

First, try commenting out your error handling code until you get your code working. Right now, if you have any errors, the error handler will simply tell you that an error occurred, but your code will not stop at the point where it cannot execute.

Just place a single quote (') in front of the "On Error GoTo errHan" line to comment out that line and disable the error handling.

Later you can enable your error handling by removind the single quote.

When you try running you code, it may now stop at a spcific line with an error message. You can then start to find out exactly what has gone wrong.

 

[Mr. B]

Actully after looking at your code in more detail, you have many things that are wrong.

Here is a modified version (without any error handling) that you can put in your module, replacing your existing code. I have actually tested this code and it works to open the specified file, rename the specified sheet, do a little formatting and then save and close the workbook and close Excel.

Public Function FormatExcelBasic(fileIn As String, sheetIn As String)
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim xlRange As Object
Dim lngLastRow As Long
Dim strCell As String
Set xlApp = CreateObject("Excel.Application")
'for testing, make the applicaiton visible
xlApp.Visible = True
Set xlBook = xlApp.Workbooks.Open(fileIn)
Set xlSheet = xlBook.Sheets(sheetIn)
xlBook.Sheets(sheetIn).Name = "New Sheet Name"
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
Set xlSheet = xlBook.Sheets("New Sheet Name")
'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", "L18").Select
Set xlRange = Range("A2", "L18")
'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
xlBook.Save
Set xlRange = Nothing
Set xlSheet = Nothing
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing
End Function

When you are attempting something like this, I would suggest that you start out rather simple and then starting building and expanding your actions and formatting after you know you have something that at its basic lever works. When you start out with too much in your code to start with, you then find yourself rather lost in the process.

Give this code a try, make sure it runs and does what you expect, and then go from there.

 

[Summer123]

Hello Mr.B thank you for your response. I see your point on running with it slow... i did copy your modified code and replaced mine with it but when i click the button, no spreadsheet is generated and nothing happens...am i doing something wrong?

 

[Mr. B]

When reading your original code attempt, I just assumed you were opening an existing file because you were exporting your data to a specific worksheet. YOu were passing a complete path and filename and the worksheet name that was to be renamed before the formatting.

The code I provided is designed to open an existing workbook (like a template type file or perhaps the file to which you just exported your Access data to) and then rename the worksheet with the name that is passed in when the function is called. It then applies some formatting to a selected range, saves the workbook and exits Excel.

My code does not create a new document. You will need to modify the code to do that.

 

[Summer123]

i went back to my basic code and still doesnt generate the spreadsheet...there has to be something wrong here..please help...it seems like i am going backwards instead of the other way around...

 

[Summer123]

sorry didnt read your post earlier... but i went back to the following and it doesnt import the query data into excel like it did before..

Option Compare Database
Function FN() As String
FN = "C:\Workspace\SCF DB\trial\test"
End Function
Function SN() As String
SN = "testsheet"
End Function
Private Sub Command0_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "2- Missing_Data_on_01", FN, True, SN
End Sub

 

[Mr. B]

Can you attach a copy of your database so I can see what is really happeniing?

 

[Summer123]

sure here you go...
i removed the extra code...since it wasnt working.. do i really need to call a module when we want to format? cant i just call the spreadsheet under the command button? thank you very much for your help on this one... its been crazy i know and i am being a pain at this point but i do appreciate your help on this!

 

[Mr. B]

I will take a look at your database and get back to you.

 

[Summer123]

i just downloaded office 2007 do you think it has anything to do with that? the code looks right and the transferspreadsheet command has no error (at least from what i see) so not sure what else it can be??

 

[Mr. B]

I will do my testing using 2007 and we will see.

 

[Summer123]

ok so i think i may know this one... on office 2007 i get this warning up at the top of navigation bar that says "Security Warning" certain content in the database has been disabled" then a button that says "Option" - when i click on it it bring another window and it has the follwing 2 options "help protect me from unknown content" and then other "enable this content" - if i say enable the button works and the spreadsheet is generated....my question now is do you know how i can get rid of this warning completely?? i dont want the use to have to go through this every time... its funny how i figured one thing out... i guess i am getting the hang of this whole access thing...

 

[Mr. B]

Two things you need to do. In Access 2007, click on the large round button (the Office Button) at the upper left corner of the Access window to display a options dialog box. Then click the "Access Options" button at the lower right of that dialog box. This will display the "Access Options" dialog box. Click on the "Trust Center" option from the options listed at the left side. Click on the "Trues Cntere Settings ..." button. In the Trust Center, click on the "Trusted Locations" option from the options list on the left. Use ths options presented to add the location (path) where your database is located. You can add as many locations as you need.

Next, click on the "Macro Settings" option from the options listed on the left. Make sure the the last option: "Enable all macors (not reccommended; potentially dangerous code can run)" is selected.

Click the OK button. Click the next OK button. You will be promted that you need to close and reopen your database before your changes will take effect.

Close and reopen your database and you should no longer have the problem you describe.