accessfever
Registered User.
- Local time
- Today, 03:21
- Joined
- Feb 7, 2010
- Messages
- 101
I have a simple Access module to download a query's output by BU field. There are 15 BUs so the module will create 15 excel files. It works well. But now people wanted to combine two or three BUs together in an excel file due to their relatively smaller records. Any suggestion to change the codes I have below?
Below is my short version of the codes:
Private Sub cmdQueryBU_Click()
Dim counterBeg As Integer
Dim counterEnd As Integer
Dim buFnum(1 To 15) As String
buFnum(1) = "AX"
buFnum(2) = "BG"
counterBeg = 1
counterEnd = 1
Do While counterBeg <= 3
cboBU = buFnum(counterEnd)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "QrydataByBU", "C:\Product BU- " & buFnum(counterEnd)".XLS"
counterBeg = counterBeg + 1
counterEnd = counterEnd + 1
Loop
counterBeg = 1
counterEnd = 1
MsgBox ("The transfer has been completed!")
End Sub
Below is my short version of the codes:
Private Sub cmdQueryBU_Click()
Dim counterBeg As Integer
Dim counterEnd As Integer
Dim buFnum(1 To 15) As String
buFnum(1) = "AX"
buFnum(2) = "BG"
counterBeg = 1
counterEnd = 1
Do While counterBeg <= 3
cboBU = buFnum(counterEnd)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "QrydataByBU", "C:\Product BU- " & buFnum(counterEnd)".XLS"
counterBeg = counterBeg + 1
counterEnd = counterEnd + 1
Loop
counterBeg = 1
counterEnd = 1
MsgBox ("The transfer has been completed!")
End Sub