Run Multiple queries using code and only export those with true values to excel

[Summer123]

Cbrighton/vbaInet, please help...i think we're almost there if i can get this last thing to work so it doesnt override my sheet everytime all the queries are run... i want them so that they export out to different sheets...i appreciate all the help you and vbaInet has given me!!!

 

[CBrighton]

http://msdn.microsoft.com/en-us/library/aa164087(v=office.10).aspx

The Microsoft website has a help page for TransferSpreadsheet which details the arguments.

It tells you the range argument doesn't apply to exporting but it does. It gives an example of how it works with importing.

 

[Summer123]

So if I do that it gives me an error that "run time error 3010 Table A1:Z90 already exists"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, ctl.Name, fileIn, True, "A1:Z90"

is there something else i should refer to so it goes to the next sheet?

 

[CBrighton]

Set the worksheet names to match the query names and try:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, ctl.Name, fileIn, True, ctlName & "!"

 

[Summer123]

ok so i changed it to this
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, ctl.Name, fileIn, True, ""

and its exporting only the ones with true results if i picked more then 5 but the issue it always send the first query even if it has results or not...but the rest work fine and only send it if there are no results...

here is the code

Select Case ctl.ControlType
Case acCheckBox
If ctl = True Then
Set rst = db.OpenRecordset(ctl.Name)
If Not rst.EOF Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, ctl.Name, fileIn, True, ""
End If
rst.Close
Set rst = Nothing
End If
End Select

 

[Summer123]

sorry quick clarification above... (was typing too fast)

2 questions

1.) if i pick all 60 queries it only exports those that have true results and the queries with no results are left alone.... but the 1st query that i have is always exported even though it does not have any results... not sure why?

2.) also if i choose queries that have no results then I want it so that it generates a message that states no errors and end the code so that it doesnt go into formatting code that i have...

Dim db As DAO.Database
Set db = CurrentDb
Dim rst As DAO.Recordset
Dim MyForm As Form
Set MyForm = Forms!QC_Query_form
For Each ctl In MyForm.Controls
    Select Case ctl.ControlType
    Case acCheckBox
        If ctl = True Then
            Set rst = db.OpenRecordset(ctl.Name)
            If Not rst.EOF Then
                DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, ctl.Name, fileIn, True, ""
            End If
            rst.Close
            Set rst = Nothing
        End If
    End Select
Next ctl
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(fileIn) [B][COLOR=red]'this is where its trying to open the spreadsheet but since no results in the case loop there is no spredsheet and so the error is generate saying cant find the file[/COLOR][/B]
Dim intCountofSheets, intCurrentSheet As Integer
intCountofSheets = xlBook.Sheets.Count
intCurrentSheet = 1

Do While intCurrentSheet <= intCountofSheets
xlBook.Worksheets(intCurrentSheet).Activate
With xlApp.ActiveSheet
.Columns.Select

 

[CBrighton]

sorry quick clarification above... (was typing too fast)

2 questions

1.) if i pick all 60 queries it only exports those that have true results and the queries with no results are left alone.... but the 1st query that i have is always exported even though it does not have any results... not sure why?

Don't know, it should work the same for all queries.

2.) also if i choose queries that have no results then I want it so that it generates a message that states no errors and end the code so that it doesnt go into formatting code that i have...

See below, if there is no spreadsheet when no reports were run then checking for the existance of the spreadsheet should be enough, however you may want to delete the file at the beginning of this sub to ensure that an old file will not be found by the VBA.

Dim db As DAO.Database
Set db = CurrentDb
Dim rst As DAO.Recordset
Dim MyForm As Form
Set MyForm = Forms!QC_Query_form
For Each ctl In MyForm.Controls
    Select Case ctl.ControlType
    Case acCheckBox
        If ctl = True Then
            Set rst = db.OpenRecordset(ctl.Name)
            If Not rst.EOF Then
                DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, ctl.Name, fileIn, True, ""
            End If
            rst.Close
            Set rst = Nothing
        End If
    End Select
Next ctl
Dim strFileName as string
strFileName = Dir("[B]C:\SpreadsheetName.xls[/B]")
If strFileName = "[B]SpreadsheetName.xls[/B]" Then
    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Open(fileIn) 
    Dim intCountofSheets, intCurrentSheet As Integer
    intCountofSheets = xlBook.Sheets.Count
    intCurrentSheet = 1
 
    Do While intCurrentSheet <= intCountofSheets
    xlBook.Worksheets(intCurrentSheet).Activate
    With xlApp.ActiveSheet
        .Columns.Select
Else
    msgbox "No reports with records"
End If

[/QUOTE]

 

[Summer123]

thank you...almost there
where would i put the else if message?? i tried few places but not successfull it gives me the message and create the spreadsheet and doesnt format it.. i am sure i goofing it up some how...

If fileIn = "Record 01-05.xls" Then
    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Open(fileIn)
    Dim intCountofSheets, intCurrentSheet As Integer
    intCountofSheets = xlBook.sheets.Count
    intCurrentSheet = 1

    Do While intCurrentSheet <= intCountofSheets
    xlBook.Worksheets(intCurrentSheet).Activate
        With xlApp.ActiveSheet
            .Columns.Select
            .Columns.EntireColumn.AutoFit
            .Rows.Select
            .Rows(1).Font.Bold = True
            .Rows(1).Font.Size = 10
            .Rows(1).Font.Name = "Verdana"
            .Range("A1").Select 'Selects first cell to deselect the rest of the sheet.
        End With
    intCurrentSheet = intCurrentSheet + 1
    Loop
    xlBook.Worksheets(1).Activate 'Returns the selection to the first worksheet.
    xlBook.Save
    xlBook.Close
    xlApp.Quit
    Set xlBook = Nothing
    Set xlApp = Nothing

is it after the set xlApp = nothing or before it?

 

[CBrighton]

I put the message in my code posted above.

There's an If statement with the message box in the Else section.

If a file was created, open it and do the formatting stuff. If not display a messagebox.

 

[Summer123]

ok sorry my bad i forgot the DIR ..its working fine now... now running into how to delete the spreadsheet if one already exists... should i use KILL to delete the spreadsheet?

 

[Summer123]

can you please help me on how to delete a spreadsheet if one already exists?

 

[Summer123]

I saw this code but when i try to implement in my code it gives me an error
If Dir(strFileName) <> "" Then Kill strFileName

but doesnt work for me... it doesnt delete the spreadsheet and the old results are still there... please help