Solved Exporting Data from Access to Excel in format (1 Viewer)

Leo_Polla_Psemata

Registered User.
Local time
Today, 07:39
Joined
Mar 24, 2014
Messages
364
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?
 

Leo_Polla_Psemata

Registered User.
Local time
Today, 07:39
Joined
Mar 24, 2014
Messages
364
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
Code:
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)


Code:
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
 
Last edited by a moderator:

Gasman

Enthusiastic Amateur
Local time
Today, 14:39
Joined
Sep 21, 2011
Messages
14,053
After you have carried out all the processes you have working successfully.? :confused:

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

Leo_Polla_Psemata

Registered User.
Local time
Today, 07:39
Joined
Mar 24, 2014
Messages
364
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

Registered User.
Local time
Today, 07:39
Joined
Mar 24, 2014
Messages
364
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

MVP / VIP
Local time
Today, 14:39
Joined
Jan 14, 2017
Messages
18,186
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

Registered User.
Local time
Today, 07:39
Joined
Mar 24, 2014
Messages
364
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

Enthusiastic Amateur
Local time
Today, 14:39
Joined
Sep 21, 2011
Messages
14,053
That will not work? :confused:

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

Code:
 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

Registered User.
Local time
Today, 07:39
Joined
Mar 24, 2014
Messages
364
That will not work? :confused:

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

Code:
 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

Enthusiastic Amateur
Local time
Today, 14:39
Joined
Sep 21, 2011
Messages
14,053
Firstly, you are not understanding concatenation that well?
Code:
xlBook.SaveAs "C:\Users\peddo\Desktop\BkList" & "" & "StarDur_BkList" & "_" & VOY & ".xlsx"
onlly needs to be
Code:
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
Code:
"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

Code:
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

Enthusiastic Amateur
Local time
Today, 14:39
Joined
Sep 21, 2011
Messages
14,053
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?

Code:
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
 
Last edited:

Leo_Polla_Psemata

Registered User.
Local time
Today, 07:39
Joined
Mar 24, 2014
Messages
364
Hi there

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

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

Enthusiastic Amateur
Local time
Today, 14:39
Joined
Sep 21, 2011
Messages
14,053
Is PIR meant to be a variable?
 

Leo_Polla_Psemata

Registered User.
Local time
Today, 07:39
Joined
Mar 24, 2014
Messages
364
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

Enthusiastic Amateur
Local time
Today, 14:39
Joined
Sep 21, 2011
Messages
14,053
Code:
Range("D4").Formula = "=SUMIF(K10:K3000," & """=PIR""" & ",H10:H3000)" 
Range("D4").Formula = "=SUMIF(K10:K3000," & """<>PIR""" & ",H10:H3000)"
 

Leo_Polla_Psemata

Registered User.
Local time
Today, 07:39
Joined
Mar 24, 2014
Messages
364
Code:
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

Registered User.
Local time
Today, 07:39
Joined
Mar 24, 2014
Messages
364
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

Enthusiastic Amateur
Local time
Today, 14:39
Joined
Sep 21, 2011
Messages
14,053
Have you tried that.?

Most times you need to fully qualify the property path if that does not work.?
Code:
App.ActiveWindow.FreezePanes = True
where App is the name of you Excel application object

You would also need to select the row
Code:
Rows("10:10").Select
which you would have got from the macro recorder?
That will likely need to be fully qualified as well.?

HTH
 

Users who are viewing this thread

Top Bottom