Use MS Access VBA to Manipulate Excel data (1 Viewer)

Myrenger1

New member
Local time
Today, 11:27
Joined
Jan 16, 2020
Messages
5
Hi, I have gotten as far as exporting my Table into an Excel sheet and opening said sheet.

However ... Anything else I try after does not seem to work; I need some code to take the data in the Excel sheet and transpose it into a new sheet and delete the old sheet. Then if possible write some further code to change the layout and add extra rows etc ...

My current code looks like this:

Code:
Option Compare Database

Sub exportToXl()

    DoCmd.SetWarnings False
    
    Dim dbTable As String
    Dim xlWorksheetPath As String
    
'    MsgBox CurrentDb.Updatable
   
    xlWorksheetPath = MyDocsPath & "\Daily_Export.xlsx"

    MsgBox xlWorksheetPath
    
    dbTable = "Daily"
    
' Exports the data from the table into an "xlsx" file on the Desktop
    
    DoCmd.TransferSpreadsheet transfertype:=acExport, spreadsheettype:=acSpreadsheetTypeExcel12Xml, tablename:=dbTable, FileName:=xlWorksheetPath, hasfieldnames:=True
    
' Opens the Excel Workbook

    Dim xlApp As Object

    Set xlApp = CreateObject("Excel.Application")
        xlApp.Visible = True
        xlApp.Workbooks.Open xlWorksheetPath
    
    Set xlApp = Nothing
    
        
End Sub

' Identify's the users profile name to allow for export to Desktop

Public Function MyDocsPath() As String

    MyDocsPath = Environ$("USERPROFILE") & "\Desktop"

End Function
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:27
Joined
Sep 21, 2011
Messages
14,051
Why not create the macro in Excel and then just run that from Access.?

I generally start off with the Macro Recorder in Excel and then tweak to suit.

Then that can be tested to the hilt in Excel then called from Access VBA.?
 

Myrenger1

New member
Local time
Today, 11:27
Joined
Jan 16, 2020
Messages
5
I have tried that as well, but because the file is exported to .xlsx it won't run the Macro. But I haven't been unable to export it to .xlsm because it throws up an error saying the Object is in Read-Only.
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:27
Joined
Sep 21, 2011
Messages
14,051
OK, then do all the hard work in Excel then copy and adjust for Access.

That is what I have done in the past.

I had a macro that would send emails. Copied it to Access and then modfiied the object names to fit Access VBA.

That way you still get to test in Excel. You can put the macro in a Personal.xslb file or any xlsm file and run from there.

HTH

Edit: Does it need to be xlsx as an xls file would work with a macro?
 

Myrenger1

New member
Local time
Today, 11:27
Joined
Jan 16, 2020
Messages
5
Would you have any example code for me to look through?

That would be great.

Thanks
 

Myrenger1

New member
Local time
Today, 11:27
Joined
Jan 16, 2020
Messages
5
So I have found that as long as I have an existing .xlsm sheet on my desktop that is named the same as the workbook it works and opens.

I just need the code now to run the Macro in the Excel Sheet. This is the part I struggled with before. Any help?

Thanks,
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:27
Joined
Sep 21, 2011
Messages
14,051
You are confusing workbooks and sheets?

I just tested

Code:
Sub SendTable(strTable)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, strTable, "C:\Users\Paul\Documents\SSAFA\Test.xlsm", True, "Sheet1"

End Sub

and that trasnferred the table Transactions to a worksheet called Sheet1 which did not exist initially.

As I mentioned before, use the Recorder to record your steps to format as you require. Perhaps write the steps down first, or do it in stages and then combine the macros. That is what I do.

To run the macro try this method

https://access-excel.tips/run-excel-macro-from-access-vba/

HTH
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 11:27
Joined
Jul 9, 2003
Messages
16,245
I realise that you are getting some excellent and knowledgeable help, so I hope I'm not muddying the waters. I thought I should point you in the direction of some code written by one of our illustrious former members Bob Larson, who has since retired I believe.

https://btabdevelopment.com/export-a-table-or-query-to-excel/

I found it extremely useful and adaptable to my needs. The below link points to an example where I have used the code and I think at the bottom of the web-page is a slightly modified version of it.
https://www.niftyaccess.com/make-excel-sheets-from-access-table/

If you would like a copy of the example database that takes the Data/Information from a Table and put it into Separate Excel Sheets, drop me a line and I will let you know how you can get hold of it...
 
Last edited:

Users who are viewing this thread

Top Bottom