UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Exporting, Office 2013    
 
   
mike60smart
post Jul 19 2019, 01:06 PM
Post#1


UtterAccess VIP
Posts: 13,323
Joined: 6-June 05
From: Dunbar,Scotland


Hi Everyone

I have the following Excel file which is received every week.

Attached File  Import.zip ( 6.9K )Number of downloads: 4


Is it possible to then select all records for a Specific Case Number and Export to a Folder. Then Loop through the records and do the same for each Specific Case Number?

Any help appreciated


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
June7
post Jul 19 2019, 01:23 PM
Post#2



Posts: 741
Joined: 25-January 16



I would use Access with link to the Excel file.

You want each case exported to its own Excel file?

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
mike60smart
post Jul 19 2019, 01:35 PM
Post#3


UtterAccess VIP
Posts: 13,323
Joined: 6-June 05
From: Dunbar,Scotland


Hi June7

Yes if it is at all possible


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
June7
post Jul 19 2019, 01:38 PM
Post#4



Posts: 741
Joined: 25-January 16



Certainly possible and simple with Access as I suggested.

Can manually set static filter in query then use External Data tab on ribbon to manually export each case or automate with VBA.

Review http://www.accessmvp.com/KDSnell/EXCEL_Export.htm


This post has been edited by June7: Jul 19 2019, 01:51 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
mike60smart
post Jul 19 2019, 01:55 PM
Post#5


UtterAccess VIP
Posts: 13,323
Joined: 6-June 05
From: Dunbar,Scotland


Hi June7

I used the following Code:-

CODE
Private Sub cmdExport_Click()
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and field names
' with the real names of the EmployeesTable table and the ManagerID field
strSQL = "SELECT DISTINCT [Case Number] FROM tblImports;"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Now loop through list of Case Numbers values and create a query for each Case Number
' so that the data can be exported -- the code assumes that the actual names
' of the Case Numbers are in a lookup table -- again, replace generic names with
' real names of tables and fields
If rstMgr.EOF = False And rstMgr.BOF = False Then
      rstMgr.MoveFirst
      Do While rstMgr.EOF = False

' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
            strMgr = DLookup("Case Number", "tblImports", _
                  "Case Number = " & rstMgr![Case Number].Value)

' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
            strSQL = "SELECT * FROM tblImports WHERE " & _
                  "Case Number = " & rstMgr![Case Number].Value & ";"
            Set qdf = dbs.QueryDefs(strTemp)
            qdf.Name = "q_" & strMgr
            strTemp = qdf.Name
            qdf.SQL = strSQL
            qdf.Close
            Set qdf = Nothing

' Replace C:\FolderName\ with actual path
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel2xlm, _
                  strTemp, "C:\Exports\" & strMgr & Format(Now(), _
                  "MMddyyyy_hhnn") & ".xlsx"

            rstMgr.MoveNext
      Loop
End If

rstMgr.Close
Set rstMgr = Nothing

dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
End Sub


When I try to run it I get the following error:-

Attached File  error.JPG ( 49.76K )Number of downloads: 10

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
Doug Steele
post Jul 19 2019, 05:18 PM
Post#6


UtterAccess VIP
Posts: 22,184
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Slight typo, Mike. It should be acSpreadsheetTypeExcel12xml (According to your error message, you have ...xlm)

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
mike60smart
post Jul 20 2019, 04:12 AM
Post#7


UtterAccess VIP
Posts: 13,323
Joined: 6-June 05
From: Dunbar,Scotland


Hi Doug

Many thanks for the spot.

Works a treat.

June7

Many thanks for the link. Much appreciated

cheers.gif

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
Marina Telly
post Jul 22 2019, 07:46 AM
Post#8



Posts: 2
Joined: 22-July 19



Yes! Many thanks for the link!
It was helpful for me
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    25th August 2019 - 12:15 AM