Results 1 to 7 of 7
  1. #1
    nggman is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    27

    Hide column with query

    I have a handy vba module where I run several access queries and then update a specific tab in an Excel spreadsheet. It works well. I have one hurdle I need to get over. The amount of columns in the query will change every month because I am pulling monthly data for the entire year. When choosing the columns in the design part of the query I can't choose specific columns for this reason. If I choose select * I get all the columns. This works EXCEPT there is one column I need pulled for sorting purposes but do NOT want to display. If I am in Access, I can just right click on that column and choose 'Hide Fields'. The next time I run the query in Access it hides the field. BUT, I am calling this query from a VBA module and the column does NOT get hidden. I have included my code from VBA that runs the query and updates the Excel spreadsheet. I am not sure if there is a way to hide this specific column with the way I am doing this.


    Set rsQuery1 = dbs.OpenRecordset("SHEET3ANALG1")


    targetWorkbook.Worksheets("Sheet3").Range("E2").Co pyFromRecordset rsQuery1

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Are you trying to avoid copying the data to excel or just hide the excel columns? Would this help? https://analysistabs.com/excel-vba/h...mns-worksheet/

  3. #3
    nggman is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    27
    Avoid copying that column to Excel. I would like to do it in my Access VBA Module because it is a nice automated process.

  4. #4
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    My thinking is that because access likes to work with records (rows) of data and not "columns" (spreadsheet concept, not really db) it will be easier to code to just delete the unwanted columns from excel after you've pasted the data. Otherwise you'll either need a few more queries to further manipulate the data or you'll need to loop through your query and copy cell by cell.

    You can delete columns from the excel spreadsheet from your Access VBA module like so:

    Code:
    Public Sub ExportQuery()
    On Error GoTo ErrHandler
        Dim dbs As DAO.Database
        Dim rsQuery1 As DAO.Recordset
        Dim excelApp As Object
        Dim targetWorkbook As Object
        
        Set dbs = CurrentDb
        Set rsQuery1 = dbs.OpenRecordset("SHEET3ANALG1")
        Set excelApp = CreateObject("Excel.application", "")
        Set targetWorkbook = excelApp.workbooks.Open("C:\WORKBOOK_FILE_NAME_GOES_HERE.xlsx")
        excelApp.Visible = True
        targetWorkbook.Worksheets("Sheet3").Range("E2").CopyFromRecordset rsQuery1
        
        'This line will delete Column F
        targetWorkbook.Worksheets("Sheet3").Columns(6).EntireColumn.Delete
        
        rsQuery1.Close
        dbs.Close
    
    ExitHandler:
        Set rsQuery1 = Nothing
        Set dbs = Nothing
        Set targetWorkbook = Nothing
        Set excelApp = Nothing
        Exit Sub
        
    ErrHandler:
        MsgBox "error #" & Err.Number & " - " & Err.Description
        Resume ExitHandler
    End Sub
    All you need to do is add some logic to loop through the columns to decide which ones to delete.

    If that's still not suitable we can look at how to loop through the data cell by cell or manipulate your query. If that's the case it would be helpful if you posted some example data and your query(s).

  5. #5
    nggman is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    27
    Thank you! This works. Except I realize I need to shift the data (after column 7) to the left one column as opposed to deleting the whole column 7. Is there something like EntireColumn.Delete that shifts the data?

    Something like this might work. I am not sure how to 'declare' the Cells though.

    For i = 1000 To 1 Step -1
    If (Cells(i, 7).Value Like "$*") Then
    Cells(i, 7).Delete shift:=xlToLeft
    End If
    Next i

  6. #6
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Code:
    Public Sub ExportQuery()
    On Error GoTo ErrHandler
        Dim dbs As DAO.Database
        Dim rsQuery1 As DAO.Recordset
        Dim excelApp As Object
        Dim targetWorkbook As Object
        Dim ws As Object
        Dim i As Integer
        
        Set dbs = CurrentDb
        Set rsQuery1 = dbs.OpenRecordset("SHEET3ANALG1")
        Set excelApp = CreateObject("Excel.application", "")
        Set targetWorkbook = excelApp.workbooks.Open("C:\Users\Kyle\Desktop\DBTEST\workbook.xlsx")
        excelApp.Visible = True
        
        Set ws = targetWorkbook.Worksheets("Sheet3")
        
        ws.Range("E2").CopyFromRecordset rsQuery1
        'This will delete Column F
        'ws.Columns(6).EntireColumn.Delete
        
        
        For i = 1000 To 1 Step -1
            If ws.cells(i, 7).Value Like "$*" Then
                ws.cells(i, 7).Delete 1   'delete and shift left
                
            End If
        Next i
        
        rsQuery1.Close
        dbs.Close
        
    
    ExitHandler:
        Set rsQuery1 = Nothing
        Set dbs = Nothing
        Set ws = Nothing
        Set targetWorkbook = Nothing
        Set excelApp = Nothing
        Exit Sub
        
    ErrHandler:
        MsgBox "error #" & Err.Number & " - " & Err.Description
        Resume ExitHandler
    End Sub

  7. #7
    nggman is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    27
    Thanks!!!!!!!!!!!!!!!!!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 07-03-2017, 09:10 AM
  2. Hide a column in a report based on user rights
    By whisp0214 in forum Reports
    Replies: 6
    Last Post: 06-29-2017, 03:11 PM
  3. Replies: 4
    Last Post: 12-29-2015, 03:25 PM
  4. Replies: 9
    Last Post: 03-02-2015, 06:05 PM
  5. Replies: 1
    Last Post: 03-12-2014, 03:13 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums