Solved Exporting Data from Access to Excel in format

[Leo_Polla_Psemata]

At first, i was in need to export from access a formatted excel report
and i reached my goal. The thread is here.
https://www.access-programmers.co.uk/forums/showthread.php?t=308329


In this address i found the code which i fixed to my needs.

http://accessjitsu.com/2015/09/13/c...g-data-from-microsoft-access-to-excel-part-2/


With this bit of code, i can change the name of excel sheet,
instead of "discount", i have used a field and every report sheet gets a meaningful name

xlApp.Visible = False
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)

With xlSheet
.Name = "Discount"
.Cells.Font.Name = "Calibri"
.Cells.Font.Size = 11

Can we change the name of excel file?
Instead of "book1", could we use a field which will tell excel which file name to be saved?

 

[Gasman]

Use the SaveAs option ?

https://stackoverflow.com/questions/14634453/how-to-use-workbook-saveas-with-automatic-overwrite

 

[Leo_Polla_Psemata]

Hi, suppose that the code that pushes data to excel is the below.
(original site http://accessjitsu.com/2015/09/13/c...g-data-from-microsoft-access-to-excel-part-3/)

Where should i insert this bit ?

UG Added Code Tags

Application.DisplayAlerts = False
Set xls = CreateObject("Excel.Application")
Set wb = xls.Workbooks.Add
fullFilePath = importFolderPath & "" & "A.xlsx"
wb.SaveAs fullFilePath, AccessMode:=xlExclusive, ConflictResolution:=True   
wb.Close(True)

Private Sub cmdTransfer_Click()
On Error GoTo SubError
 
    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim SQL As String
    Dim rs1 As DAO.Recordset
    Dim i As Integer
  
    'Show user work is being performed
    DoCmd.Hourglass (True)
  
    '*********************************************
    '              RETRIEVE DATA
    '*********************************************
    'SQL statement to retrieve data from database
    SQL = "SELECT PartNo, PartName, Price, SalePrice, " & _
    "(Price - SalePrice) / Price AS Discount " & _
    "FROM Parts " & _
    "ORDER BY PartNo "
  
    'Execute query and populate recordset
    Set rs1 = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)
  
    'If no data, don't bother opening Excel, just quit
    If rs1.RecordCount = 0 Then
        MsgBox "No data selected for export", vbInformation + vbOKOnly, "No data exported"
        GoTo SubExit
    End If
  
    '*********************************************
    '             BUILD SPREADSHEET
    '*********************************************
    'Create an instance of Excel and start building a spreadsheet
  
    'Early Binding
    Set xlApp = Excel.Application
  
    xlApp.Visible = False
    Set xlBook = xlApp.Workbooks.Add
    Set xlSheet = xlBook.Worksheets(1)
 
    With xlSheet
        .Name = "Discount"
        .Cells.Font.Name = "Calibri"
        .Cells.Font.Size = 11
  
        'Set column widths
        .Columns("A").ColumnWidth = 13
        .Columns("B").ColumnWidth = 25
        .Columns("C").ColumnWidth = 10
        .Columns("D").ColumnWidth = 10
        .Columns("E").ColumnWidth = 2
        .Columns("F").ColumnWidth = 10
        
        'Format columns
        .Columns("A").NumberFormat = "@"
        .Columns("C").NumberFormat = "$#,##0.00;-$#,##0.00"
        .Columns("D").NumberFormat = "$#,##0.00;-$#,##0.00"
        .Columns("F").NumberFormat = "#,##0.0#%;-#,##0.0#%"
      
        'build column headings
        .Range("A4").Value = "Part Number"
        .Range("B4").Value = "Part Name"
        .Range("C4").Value = "Price"
        .Range("D4").Value = "Sale Price"
        .Range("F4").Value = "Discount"
 
        'provide initial value to row counter
        i = 5
        'Loop through recordset and copy data from recordset to sheet
        Do While Not rs1.EOF
      
            .Range("A" & i).Value = Nz(rs1!PartNo, "")
            .Range("B" & i).Value = Nz(rs1!PartName, "")
            .Range("C" & i).Value = Nz(rs1!Price, 0)
            .Range("D" & i).Value = Nz(rs1!SalePrice, 0)
            .Range("F" & i).Value = Nz(rs1!Discount, 0)
          
            i = i + 1
            rs1.MoveNext
    
        Loop
      
      
    End With
 
 
SubExit:
On Error Resume Next
 
    DoCmd.Hourglass False
    xlApp.Visible = True
    rs1.Close
    Set rs1 = Nothing
 
    Exit Sub
  
SubError:
    MsgBox "Error Number: " & Err.Number & "= " & Err.Description, vbCritical + vbOKOnly, _
        "An error occurred"
    GoTo SubExit
  
End Sub

 

[Gasman]

After you have carried out all the processes you have working successfully.?

You would only need the SaveAs line and perhaps the DisplayAlerts if the file already exists?

 

[Leo_Polla_Psemata]

Hi, many thanks


I have managed to enter this line in my code and save the excel as "A"


'fullFilePath = importFolderPath & "" & "A.xlsx"
wb.SaveAs fullFilePath, AccessMode:=xlExclusive, ConflictResolution:=True


Now, how could someone replace the "A" with a field from the code and save the excel with this name ?



Suppose we have a field "productCode" , the procust code is ABC1 , the excel file will be saved as ABC1. , next product XYZ1, the excel will be saved as XYZ1 etc

 

[Leo_Polla_Psemata]

Okay, i have just found it, I made some test in line and i found it



I put the A outside quotes


fullFilePath = importFolderPath & "" & "A.xlsx"

fullFilePath = importFolderPath & "" & A".xlsx"

 

[isladogs]

I'm somewhat confused by your last answer.
In response to the earlier question, set a string variable strCode to your product code e.g. XYZ1. Then use

fullFilePath = importFolderPath & strCode & ".xlsx"

 

[Leo_Polla_Psemata]

I'm somewhat confused by your last answer.
In response to the earlier question, set a string variable strCode to your product code e.g. XYZ1. Then use

fullFilePath = importFolderPath & strCode & ".xlsx"

Hi, i appreciate your concer.
Apologies, i am not a native english speaker, i an mot IT either, i don't have any programing knowledge.
Only few experience, i would say by 80% experience and knowledge i have gained from this site, thank you all.
I find ready code which i can tune to my needs.

Now, in my code, there is a field A, report is dependent on this field A.

I used this importFolderPath & "" & "A.xlsx" but all files were saved with "A".
Then
I used this importFolderPath & "" & A".xlsx" , A is aoutside quotes and all files are "saved a"s with the data which is housed in the A field.
Perfect.

 

[Gasman]

That will not work?

You need to concatenate the path and the filename and the extension
importFolderPath = "C:\temp"
A = "Test"

 importFolderPath & A & ".xlsx"

that should save the file as "C:\Temp\Test.xlsx"

You really need to give more meaningful names to cotrols/variables, as it would be so easy to lose that A in amongst all your code?

HTH

 

[Leo_Polla_Psemata]

That will not work?

You need to concatenate the path and the filename and the extension
importFolderPath = "C:\temp"
A = "Test"

 importFolderPath & A & ".xlsx"

that should save the file as "C:\Temp\Test.xlsx"

You really need to give more meaningful names to cotrols/variables, as it would be so easy to lose that A in amongst all your code?

HTH

Sure Gasman. i didn't copy paste the whole line, "importfolderpath" and A is examples.
It works ,

The code line is this one
xlBook.SaveAs "C:\Users\peddo\Desktop\BkList" & "" & "StarDur_BkList" & "_" & VOY & ".xlsx", AccessMode:=xlExclusive, ConflictResolution:=True




Meaningful names, yes, i understand the importance of short and meaningful names, i fully agree with you. This "namegiving" turns out to be a really hard task.



Now
In this code i found here
https://www.access-programmers.co.uk/forums/showthread.php?t=193374
we can create an e-mail from scratch without using the docmd.sendobject methode.


How can someone add a specific attachment to this email ?

What line would you add in the code?

 

[Gasman]

Firstly, you are not understanding concatenation that well?

xlBook.SaveAs "C:\Users\peddo\Desktop\BkList" & "" & "StarDur_BkList" & "_" & VOY & ".xlsx"

onlly needs to be

xlBook.SaveAs "C:\Users\peddo\Desktop\BkList" & "StarDur_BkList" & "_" & VOY & ".xlsx"

You are concatenating zero length srings which does nothing to help you.?

Why not just use "StarDur_BkList_" instead of a separate string. The less you need to construct, the less chance to go wrong?
I would have thought your path would be

"C:\Users\peddo\Desktop\BkList\"  ??

so is that just the site removing the backslash as you have not used code tags?

For attachments something along the lines of

strJAGQuest = Environ("UserProfile") & "\" & "Documents\JAG Questions.docx"
.
.
Set olMail = Application.CreateItem(olMailItem)
olMail.Attachments.Add strJAGQuest

I have only shown the code needed for an attachment.

 

[Gasman]

Seems your last post got missed in the swap to new software.
This was my reply to that.

Yo need to select the relevant worksheet. This worked for me?

Sub Show_Gridlines()
Dim ws1 As Worksheet
Set ws1 = Worksheets("Sheet2")
'show/hide gridlines in a worksheet named Sheet2
ws1.Activate
ActiveWindow.DisplayGridlines = False
End Sub

HTH

 

[Uncle Gizmo]

Would this be of any use to you? I'm happy to let you have a free copy!

Show Hide, Export to Excel

 

[Leo_Polla_Psemata]

Hi there

The code that I have used and works is same as this example, i export to excel with formatting.

Accessjitsu.com

Now, in another similar export with formatting i want to use formulas

I have tried to type this sumif formula .
Range("D4").Formula = "=SUMIF(K10:K3000," & """ &'PIR' &""" & ",H10:H3000)" ,
the problem is that in the excel i get this
=SUMIF(K10:K3000," &'PIR' &",H10:H3000)
then i have to remove manually the extra & ' which i have in bold and make it function .
However, when i need to add the "<>Pir" ,
it just doesn't work.
Do you know which is the correct typing to reach my goal ?
The formula must give in the excel the below two

=SUMIF(K10:K3000,"PIR",H10:H3000)
and
=SUMIF(K10:K3000,"<>PIR",H10:H3000)

 

[Gasman]

Is PIR meant to be a variable?

 

[Leo_Polla_Psemata]

The k colon (from k10 to k3000) contains data of three letters (PIR or SHA or RTM or several) and i need to count how may PIR we get , how many non PIR we get.

P.s. I am not sure what "variable" means in here

 

[Gasman]

Range("D4").Formula = "=SUMIF(K10:K3000," & """=PIR""" & ",H10:H3000)" 
Range("D4").Formula = "=SUMIF(K10:K3000," & """<>PIR""" & ",H10:H3000)"

 

[Leo_Polla_Psemata]

Range("D4").Formula = "=SUMIF(K10:K3000," & """=PIR""" & ",H10:H3000)"
Range("D4").Formula = "=SUMIF(K10:K3000," & """<>PIR""" & ",H10:H3000)"

Thank you ore Gasman . ... thats it

 

[Leo_Polla_Psemata]

The last
I would like to freeze panes in line 10.
If i try the macro on excel, i get the below.
What should i do to transfer this one on the Access VBA code ?

Range("A10").Select
ActiveWindow.FreezePanes = True

 

[Gasman]

Have you tried that.?

Most times you need to fully qualify the property path if that does not work.?

App.ActiveWindow.FreezePanes = True

where App is the name of you Excel application object

You would also need to select the row

Rows("10:10").Select

which you would have got from the macro recorder?
That will likely need to be fully qualified as well.?

HTH