How to move the data to Excel

[Gunaerson]

Good Morning Guys, I have a small rookie problem here with my database. I have a database containing all states and (local government areas) Lga's in a country.
NOTE:- Each state has its own unique local government area.
I need an SQL query that would enable me to extract each state containing each LGA. Using Nigeria as an example (37 states, 770 LGA Each )
In ms Excel language, from my database containing about 400k records, I want to export 37 workbooks ( States ), all containing their respective LGAs in worksheets of the state workbook.
Would go a long way if I can be assisted with a SQL query that allows me to export these results as an .xlsx (excel) file(s)

 

[arnelgp]

it's not rookie after alll. you need some vba to assist you with that.

 

[arnelgp]

this is a rough sample:

1. create a query against your table, selecting
all fields that you want to include in the
exported excel file.
2. also include the state field.
3. while at design view of your query,
add a Criteria to state field:
Criteria: [Tempvars].[tvar_State]
4. save your query (eg, LGA List)
5. copy and paste the following code
to a Standard Module in VBA.

Public Function ExportAllLGA()
Dim rs As DAO.Recordset
Dim strPath As String
' save Excel files to your Documents
strPath = Environ("UserProfile") & "\Documents\"
' replace STATEID with the field you have for state
Set rs = CurrentDb.OpenRecordset("SELECT STATEID from yourTableName Group by STATEID;")
With rs
    If Not (.BOF And .EOF) Then .MoveFirst
    ' if your State field is numeric use this
    TempVars.Add "tvar_State", 0
    ' if your State field is string use this
    'TempVars.Add "tvar_State", "@"
    While Not .EOF
        TempVars!tvar_State = !STATEID.Value
	' if excel file already exist, delete and create new
        If Dir(strPath & TempVars!tvar_State & ".xlsx") <> "" Then _
            Kill strPath & TempVars!tvar_State & ".xlsx"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "LGA List", strPath & TempVars!tvar_State & ".xlsx", True
        .MoveNext
    Wend
    .Close
End With
Set rs = Nothing
End Function

it would be helpful if you can tell me
what is the field Name for the State
on your table, and what datatype it is.

 

[Gunaerson]

Wow thanks mate you are a legend let me try it out and give you feedback real quick.

 

[Gunaerson]

Dear sir,

Ran the code and it did work! I kept getting pop-ups to include the value of the TempVars!tvar_State. I inputted a state "ogun" and got no feedback so I cannot tell what action had occurred in the background.
Lemme explain my issue properly;

In my database table I have field names;
1.Name.
2.Age.
3.Local government area
4. State.
There are 37 states in my country and 700+ LGA's.
I am trying to write a VBA code that splits my data into 37 workbooks( a workbook per state). Each workbook would contain worksheets of their respective LGAs.
For example from my table containing 37 states.
I run my query to extract Lagos State workbook containing surulere, Ikeja, Alimosho as individual worksheets in the workbook.

 

[arnelgp]

from the code below, edit the portion
with "YourTableName" and replaced with
your correct table name.

Public Function ExportAllLGA()
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim qd As DAO.QueryDef
Dim strPath As String
Dim strSQL As String
Dim lngNumofStates As Integer
strPath = Environ("UserProfile") & "\Documents\"
On Error Resume Next
' Edit the code below and put the correct table name for "YourTableName"
Set rs = CurrentDb.OpenRecordset("SELECT State from [YourTableName] Group by State;")
With rs
    If Not (.BOF And .EOF) Then
        .MoveLast
        lngNumofStates = .RecordCount
        .MoveFirst
    End If
    While Not .EOF
        If Dir(strPath & !State & ".xlsx") <> "" Then _
            Kill strPath & !State & ".xlsx"
        
        ' Edit the code below and put the correct table name for "YourTableName"
        strSQL = "SELECT [Local government area] From [YourTableName] " & _
            "Where State=" & Chr(34) & !State & Chr(34) & " Group By [Local government area];"
        Set rs2 = CurrentDb.OpenRecordset(strSQL)
        If Not (rs2.BOF And rs2.EOF) Then
            rs2.MoveFirst
            While Not rs2.EOF
                CurrentDb.QueryDefs.Delete rs2![Local government area]
                ' Edit the code below and put the correct table name for "YourTableName"
                Set qd = CurrentDb.CreateQueryDef(rs2![Local government area], _
                    "SELECT * FROM [YourTableName] WHERE State=" & Chr(34) & !State & Chr(34) & " And " & _
                        "[Local government area]=" & Chr(34) & rs2![Local government area] & Chr(34))
                CurrentDb.QueryDefs.Append qd
                CurrentDb.QueryDefs.Refresh
                DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, (rs2![Local government area].Value & ""), strPath & !State & ".xlsx", True
                CurrentDb.QueryDefs.Delete rs2![Local government area]
                rs2.MoveNext
            Wend
        End If
        rs2.Close
        Set rs2 = Nothing
        .MoveNext
    Wend
    .Close
End With
Set rs = Nothing
MsgBox "Successfully exported " & Trim(lngNumofStates) & " State(s) to separate excel files." & vbCrLf & _
"Excel files are located at " & strPath
End Function

 

[Gunaerson]

Thank you very much, sir, unfortunately, I could not get the code to work can you please assist me with your email so I can send sample files to sir. Thanks for your continual assistance.