Export table data to excel (1 Viewer)

suryu

Member
Local time
Today, 06:18
Joined
Apr 3, 2020
Messages
86
Hi,

i am facing issues to copy headers from two table in access and paste to open workbook. KIndly help me
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:48
Joined
May 7, 2009
Messages
19,169
can you explain more, what is the header? is it the fieldname?
I think you will need vba to do this.
using Field.Name of the tabledef, you can assign it direct to the cell
of open workbook.
you may need reference to Microsoft Excel XX.X Object Library to use
automation.
 

suryu

Member
Local time
Today, 06:18
Joined
Apr 3, 2020
Messages
86
HI
yes i am trying to do vba code

1) trying to open excel workbook

set oXL= Createobject("excel.application")
oXL.visible = tRue
ser wkbk=oXl.Workbooks.add


after workbook open , i need to take columns header from two table in ms access

is there i can do with ADODB>connection?

I need only headers for template format so i think i can't do it from query.

kindly do suggest the right approach.

thank you in advance
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:48
Joined
May 7, 2009
Messages
19,169
if you only need the header, yes create a query,

select tableA.field1, tableA.field2, tableB.field1, tableB.field2 from tableA, tableB Where (1=0);

add the field you need, just like in above and set to Criteria to (1=0) so that it will not fetch any record.
save your query.

on VBA, you can open the QueryDef and recursively pull the columnname:

dim qd as DAO.QueryDef
dim fld as DAO.Field


set oXL= Createobject("excel.application")
oXL.visible = tRue
set wkbk=oXl.Workbooks.add
set sht = wkbk.sheets(1)

dim I as integer

set qd = currentdb.Querdefs("yourQueryName")
I = 1
for each fld in qd.Fields
sht.range("a" & i).Value = fld.Name
i = i + 1
next
set qd =nothing
...
 

suryu

Member
Local time
Today, 06:18
Joined
Apr 3, 2020
Messages
86
HI team ,
please help me to get vba code on below:

i have data on second sheet and i want to do validation on first sheet on the basis of second sheet.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:48
Joined
May 7, 2009
Messages
19,169
how do you want it validated?
I think you can use Worksheetfunction.VLookup() to check if a cell is in the other range.
 

suryu

Member
Local time
Today, 06:18
Joined
Apr 3, 2020
Messages
86
No, actually in second sheet only one columns data is there. i have to do drop down in first sheet in range "A2". It should capture unique values like data validation.

is there any such property to do data validation for drop down list in access using vba.

help me please.

Thank you
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:48
Joined
May 7, 2009
Messages
19,169
Vlookup is you validator.
if vlookup finds it in sheet2 then it is valid.
otherwise if an error occurs, meaning nothing
is found then what shall you do?

copy and paste this sample code a module.
replace "place your workbook here" with the path and name
of your excel workbook.
Code:
Private Sub test_45929()

    Dim xl As New Excel.Application
    Dim wb As Excel.Workbook
    Dim sh1 As Excel.Worksheet
    Dim sh2 As Excel.Worksheet

    Dim lngLastRowInSheet1 As Long
    Dim lngLastRowInSheet2 As Long

    Dim var As Variant
    Dim i As Long

    'Set wb = xl.Workbooks.Open("d:\book11.xlsx")
    Set wb = xl.Workbooks.Open("place your workbook here")
    Set sh1 = wb.Sheets(1)
    Set sh2 = wb.Sheets(2)

    'get lastrow in sh1
    lngLastRowInSheet1 = sh1.Range("A" & sh1.Rows.Count).End(xlUp).Row

    'get lastrow in sh2
    lngLastRowInSheet2 = sh2.Range("A" & sh2.Rows.Count).End(xlUp).Row

    On Error Resume Next
    For i = 2 To lngLastRowInSheet1
        var = xl.WorksheetFunction.VLookup(sh1.Range("a" & i).Value, sh1.Range("a2:a" & lngLastRowInSheet2), 1, 0)
        If Err Then
            ' error means Vlookup did not find it
            ' its entirely up to you to decide
            ' what to do.
            Err.Clear
        Else
            ' we found the data in sheet2
            ' Debug.Print var & ""
            MsgBox var & " was found in sheet2"
        End If
    Next

    wb.Close False
    xl.Quit
    Set wb = Nothing
    Set xl = Nothing

    MsgBox "done processing workbook!"
End Sub
 

suryu

Member
Local time
Today, 06:18
Joined
Apr 3, 2020
Messages
86
Vlookup is you validator.
if vlookup finds it in sheet2 then it is valid.
otherwise if an error occurs, meaning nothing
is found then what shall you do?

copy and paste this sample code a module.
replace "place your workbook here" with the path and name
of your excel workbook.
Code:
Private Sub test_45929()

    Dim xl As New Excel.Application
    Dim wb As Excel.Workbook
    Dim sh1 As Excel.Worksheet
    Dim sh2 As Excel.Worksheet

    Dim lngLastRowInSheet1 As Long
    Dim lngLastRowInSheet2 As Long

    Dim var As Variant
    Dim i As Long

    'Set wb = xl.Workbooks.Open("d:\book11.xlsx")
    Set wb = xl.Workbooks.Open("place your workbook here")
    Set sh1 = wb.Sheets(1)
    Set sh2 = wb.Sheets(2)

    'get lastrow in sh1
    lngLastRowInSheet1 = sh1.Range("A" & sh1.Rows.Count).End(xlUp).Row

    'get lastrow in sh2
    lngLastRowInSheet2 = sh2.Range("A" & sh2.Rows.Count).End(xlUp).Row

    On Error Resume Next
    For i = 2 To lngLastRowInSheet1
        var = xl.WorksheetFunction.VLookup(sh1.Range("a" & i).Value, sh1.Range("a2:a" & lngLastRowInSheet2), 1, 0)
        If Err Then
            ' error means Vlookup did not find it
            ' its entirely up to you to decide
            ' what to do.
            Err.Clear
        Else
            ' we found the data in sheet2
            ' Debug.Print var & ""
            MsgBox var & " was found in sheet2"
        End If
    Next

    wb.Close False
    xl.Quit
    Set wb = Nothing
    Set xl = Nothing

    MsgBox "done processing workbook!"
End Sub
Thanks a lot, i got an idea to work on.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:48
Joined
May 7, 2009
Messages
19,169
goodluck and hoping for the best.
 

suryu

Member
Local time
Today, 06:18
Joined
Apr 3, 2020
Messages
86
Could you tell me how to delete spaces from drop down list .
i did code like this:

with sh1.range("A2").validatio
.delete
.Add type:xlvalidateList, AlertStyle:=xlValidAlertStop, operator:xlBetween, Formula:"=sheet2!AH2:AH1000"
.IgnoreBlank=True
.IncellDropDown=True
.InputTitle=""
.ShowInput= True
.ShowError= True

End With


Using this code i am getting blank in drop down, how to remove blank

kindly help me.

Thank you
 

suryu

Member
Local time
Today, 06:18
Joined
Apr 3, 2020
Messages
86
Hi arnelgp,
i have done code in access form for commandbutton. In first click i am getting correct result but in second click i am not getting second sheet data.

is there i need to set some time or something else?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:48
Joined
May 7, 2009
Messages
19,169
is it ok for you to upload a Zip file of your db together with the Excel sheet?
 

suryu

Member
Local time
Today, 06:18
Joined
Apr 3, 2020
Messages
86
I can't upload but here is my sample code:

Code Tags Added by UG
Please use Code Tags when posting VBA Code

Please feel free to Remove this Comment
https://www.access-programmers.co.u...e-use-code-tags-when-posting-vba-code.240420/

Code:
Dim wb As object
Dim xlWS As object
Dim MyConnection As ADODB.Connection
Dim MyRecordset As ADODB.Recorset
Dim Myquery As String
Dim daoRcd As DAO.Recordset
Dim daoQueryDef As DAO.QueryDef
Dim db As DAO.Database

On Error Resume Next

set oXL= Createobject("excel.application")
oXL.visible = True
set wkbk=oXl.Workbooks.add
set sht = wkbk.sheets(1)

wb.Sheets("Sheet1").cells(1,1).value ="PO"
wb.Sheets("Sheet1").cells(1,2).value ="PO Number"
wb.Sheets("Sheet1").cells(1,3).value ="Invoice Date"
wb.Sheets("Sheet1").cells(1,4).value ="Invoice Amt"

wb.sheets("sheet1").Name ="PODetails"

wb.Sheets.Add(After:=Sheets("PODetails")).Name= ''Order"

* First time, when i am trying to execute above code, sheet2 is getting add but in second execution second sheet is not getting add
 
Last edited by a moderator:

Gasman

Enthusiastic Amateur
Local time
Today, 00:48
Joined
Sep 21, 2011
Messages
14,048
Perhaps comment out On Error Resume Next and see what happens then.?
 

suryu

Member
Local time
Today, 06:18
Joined
Apr 3, 2020
Messages
86
Hi ,

if i am doing comment on On Error Resume Next

i am getting error on below code:

wb.Sheets.Add(After:=Sheets("PODetails")).Name= ''Order"

Kindly help me
 

Users who are viewing this thread

Top Bottom