Run Excel Macro from Access (1 Viewer)

Joy83

Member
Local time
Today, 03:11
Joined
Jan 9, 2020
Messages
116
I am trying to scrape data from web from my app in access but I couldn’t find a perfect way to do it.

however It’s working perfectly from excel macro using querytable function

i am trying to run a code from access to run a macro for me to get the result into excel then take them back to my code in access
But I am getting error on the object (able(querytable)
Error: object required

Do you have any suggestions how I can make this happen




Public Function runExcelMacro(wkbookPath)
Dim url As String
url="https: //mywebsite"
Dim XL As Object
Dim table As QueryTable
Set table
Sheetl.QueryTables.Add ("URL;" & url, Sheetl.Range ("Al") )
With table
.WebSelectionType
xlSpecifiedTables
.WebTables="1"
.WebFormatting= xlWebFormattingAll
. Refresh
End With
End Function
 
Last edited:

Joy83

Member
Local time
Today, 03:11
Joined
Jan 9, 2020
Messages
116
The error I got is in (set table) line
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:11
Joined
Sep 21, 2011
Messages
14,038
The error I got is in (set table) line
You would need to supply the full object path when in Access, so create new excel instance, sheet etc.
This is automatic whilst within Excel, but outside of it, you need to qualify each object.

See also Similar threads, topic 2
 

Joy83

Member
Local time
Today, 03:11
Joined
Jan 9, 2020
Messages
116
You would need to supply the full object path when in Access, so create new excel instance, sheet etc.
This is automatic whilst within Excel, but outside of it, you need to qualify each object.

See also Similar threads, topic 2

can you clarify more please .. maybe example or a thread that can help
How to adjust the code above
 

silentwolf

Active member
Local time
Today, 03:11
Joined
Jun 12, 2009
Messages
545
Hi,

Something like that

Code:
'Public Sub WriteExcelWorkbook()
'    Dim appExcel As Excel.Application
'    Dim wkbExcel As Excel.Workbook
'    Dim wksExcel As Excel.Worksheet
'    Dim rngExcel As Excel.Range
'
'    Dim intSpalte As Integer
'
'    Set appExcel = GetObject(,"Excel.Application")
'    If appExcel Is Nothing Then
'        MsgBox "Kein Excel gefunden!", vbCritical
'        Exit Sub
'    Else
'        appExcel.Visible = False
'        Set wkbExcel = appExcel.Workbooks.Add()
'        Set wksExcel = wkbExcel.Worksheets(1)
'
'        With wksExcel.Cells(1, intSpalte)
            'Your Code'
'        End With
'
'
'        Set rngExcel = wksExcel.Range("A2")
'        wksExcel.Cells.EntireColumn.AutoFit
'
'        wkbExcel.SaveAs
'        wkbExcel.Close
'        appExcel.Quit
'    End If
'End Sub
 

Joy83

Member
Local time
Today, 03:11
Joined
Jan 9, 2020
Messages
116
You would need to supply the full object path when in Access, so create new excel instance, sheet etc.
This is automatic whilst within Excel, but outside of it, you need to qualify each object.

See also Similar threads, topic 2

can you clarify more please .. maybe example or a thread that can
How to adjust the code above
 

Joy83

Member
Local time
Today, 03:11
Joined
Jan 9, 2020
Messages
116
I used the same code
I get the same error
in the line set table
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:11
Joined
Sep 21, 2011
Messages
14,038
Show your code!
We are not clairvoyants. :)
All that previous code was commented out?
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 06:11
Joined
Apr 27, 2015
Messages
6,280
I get the same error
in the line set table
Code:
Dim table As QueryTable
Set table

1. No such thing as a QueryTable in Access
2, Even if there was, you must set it to something...your "Set Table" statement is incomplete
 

Joy83

Member
Local time
Today, 03:11
Joined
Jan 9, 2020
Messages
116
Public Function runExcelMacro(wkbookPath)

Dim XlApp As Excel.Application

Dim XlBook As Excel.Workbook

Dim xlsheet As Excel.Worksheet

Set XlApp= CreateObject("Excel.Application")

Set XlBook= X1App. Workbooks.Open ("\Documents\R.xlsx")

Set xlsheet= XlBook.Worksheets ( "Sheetl")

Dim url As String

url= "https://mywebsite"

XlApp. Visible= True

Dim table As QueryTable

Set table=xlsheet.queryTables.Add("URL;" & url.xlsheet.Range ("Al") )

table. WebSelectionType

table. WebTables= "1"

table. WebFormatting=xlwebformattingall

table.Refresh

XlBook.Save

X1App. Quit

Set XlBook= Nothing

Set XlApp= Nothing



End function
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:11
Joined
Sep 21, 2011
Messages
14,038
Does that even compile?
I would expect you would need Excel.QueryTable
Remember you are using objects alien to Access, so need to qualify everything Access is not aware of.
 

Joy83

Member
Local time
Today, 03:11
Joined
Jan 9, 2020
Messages
116
Ok the code above worked correctly
I added the latest activex library
I also needed to add a delay of few seconds to be able to allow the table to refresh get the fields from excel.
its working fine now
Thanks all
 

Isaac

Lifelong Learner
Local time
Today, 03:11
Joined
Mar 14, 2017
Messages
8,738
Ok the code above worked correctly
I added the latest activex library
I also needed to add a delay of few seconds to be able to allow the table to refresh get the fields from excel.
its working fine now
Thanks all
Why don't you just leave your macro fully coded in Excel and have it accept an input parameter for the url. Save and close the workbook. Then write vba in access and make it be nothing other than declaring an Excel application or object variable, setting it to the create object method to open the Excel application, declare a workbook variable as an object in access, set it to the application's workbooks.open method, and then use application.run with a parameter passed. This is the simplest.

Rather than trying to code all that Excel VBA inside access if you said you already had a working macro in Excel
 

GPGeorge

Grover Park George
Local time
Today, 03:11
Joined
Nov 25, 2004
Messages
1,775
The few times I had to automate Excel, I usually found it far less complex to write, and test, VBA in the native Excel environment and only invoke it from the Access environment. Not always, but most of the time.
 

Joy83

Member
Local time
Today, 03:11
Joined
Jan 9, 2020
Messages
116
Why don't you just leave your macro fully coded in Excel and have it accept an input parameter for the url. Save and close the workbook. Then write vba in access and make it be nothing other than declaring an Excel application or object variable, setting it to the create object method to open the Excel application, declare a workbook variable as an object in access, set it to the application's workbooks.open method, and then use application.run with a parameter passed. This is the simplest.

Rather than trying to code all that Excel VBA inside access if you said you already had a working macro in Excel
That’s a good idea
Is there away to automatically opening excel and running the macro from access.
i usually use a button to run the macro
Can I run it once I open the excel
 

Joy83

Member
Local time
Today, 03:11
Joined
Jan 9, 2020
Messages
116
The few times I had to automate Excel, I usually found it far less complex to write, and test, VBA in the native Excel environment and only invoke it from the Access environment. Not always, but most of the time.
I want to call the excel and run the embedded code there in excel
can you advise how to do that
 

KitaYama

Well-known member
Local time
Today, 19:11
Joined
Jan 6, 2022
Messages
1,489
I want to call the excel and run the embedded code there in excel
can you advise how to do that
The following code opens a workbook and runs a macro named ClearTable in excel from an Access database.

Notes :
  • Excel file has to be saved in a trusted location.
  • The following code uses late binding. IF you use it frequently, you may want to change it to early binding.
  • First be sure the macro in excel runs without any error.
  • You can set xlapp.Visible to False to hide the excel, save and close it without user notices any change.

Code:
Sub test2()
    Dim xlapp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim MacroName As String
    Dim FileName As String

    MacroName = "ClearTable"                       ' change this to the macro you want to execute
    FileName = "D:\Orders\Copy Kedi To Sql.xlsm"   ' change this to your FileName

    Set xlapp = New Excel.Application
    Set xlBook = xlapp.Workbooks.Open(FileName)
    Set xlSheet = xlBook.Worksheets(1)

    xlapp.Visible = True
    xlSheet.Activate

    With xlapp
        .Run MacroName
    End With
End Sub
 
Last edited:

Joy83

Member
Local time
Today, 03:11
Joined
Jan 9, 2020
Messages
116
The following code opens a workbook and runs a macro named ClearTable in excel from an Access database.

Notes :
  • Excel file has to be saved in a trusted location.
  • The following code uses late binding. IF you use it frequently, you may want to change it to early binding.
  • First be sure the macro in excel runs without any error.
  • You can set xlapp.Visible=True to False to hide the excel, save and close it.

Code:
Sub test2()

    Dim xlapp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim MacroName As String
    Dim FileName As String

    MacroName = "ClearTable"                       ' change this to the macro you want to execute
    FileName = "D:\Orders\Copy Kedi To Sql.xlsm"   ' change this to the FileName
    Set xlapp = New Excel.Application
    Set xlBook = xlapp.Workbooks.Open(FileName)
    Set xlSheet = xlBook.Worksheets(1)

    xlapp.Visible = True
    xlSheet.Activate

    With xlapp
        .Run MacroName
    End With

End Sub
Thanks a lot
This is really helpful
 

Users who are viewing this thread

Top Bottom