Full Version: Modify Excel Vba To Run In Access Module?
UtterAccess Discussion Forums > Microsoft® Access > Access Modules
classicxman
I have the following codes which I run directly in Excel to delete links, delete sheets and save a new email version of the workbook. I would like to run this code from Access as part of my regular refresh batch, instead of Excel, so I can keep all my Excel files macro-free. How do I need to modify the code to do this? Muchos Gracias!
!--c1-->
CODE
Sub DeleteLinks()
    Dim Ws As Worksheet, FirstSheet As Worksheet
    Set FirstSheet = ActiveSheet
    Application.ScreenUpdating = False
    For Each Ws In Worksheets
        Ws.Activate
     'Remove formulas & external links
        With Cells
            .Select
            .Copy
            Selection.PasteSpecial Paste:=xlValues
            Application.CutCopyMode = False
        End With
     'Remove Hyperlinks
        Cells.Hyperlinks.Delete
        [A1].Select
    Next
    FirstSheet.Activate
End Sub

CODE
Sub DeleteSheet()
  Application.DisplayAlerts = False
    Sheets("SheetName1").Delete
    Sheets("SheetName2").Delete
    Sheets("SheetName3").Delete
  Application.DisplayAlerts = True
End Sub

CODE
Sub MailVersion()
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim NewFileName As String
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
        FileExtStr = ".xlsx": FileFormatNum = 51
        TempFilePath = "C:\autoemailfiles\"
        TempFileName = Range("dashboard").Value
        NewFileName = TempFileName & " " & Format$(Date, "mm-dd-yyyy")
        
        With ActiveWorkbook
            .SaveAs TempFilePath & NewFileName, FileFormat:=FileFormatNum
            .Close SaveChanges:=False
        End With
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub
thegeek
You'll need to set a reference to the Excel Object library I believe. Then you can create an Excel object in Access VBA. Here is some "air code":
im xlsObj As Object
Dim xlsRange As Excel.Range
Dim xlsSheet As Excel.Sheet
Set xlsObj = CreateObject("Excel.Application")
xlsObj.Workbooks.Open "[path to your workbook]"
With xlsObj
Set xlsSheet = .Activesheet
etc...
Bob G
here is an example of how to do your deletesheet.
CODE
Set oApp = CreateObject("Excel.Application")
    oApp.Visible = False
    oApp.Workbooks.Open FileName:=strfilename
    oApp.UserControl = True
oApp.Application.DisplayAlerts = False
oApp.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
oApp.ActiveWindow.SelectedSheets.Delete
classicxman
Thank you. I love examples!
classicxman
Any "example" for how to do the deletelinks() sub? iconfused.gif I was able to get some working code to copy/paste in the first worksheet. But I can't get it to loop through all the worksheets in the workbook.
!--c1-->
CODE
Sub DeleteLinks()
    Dim Ws As Worksheet, FirstSheet As Worksheet
    Set FirstSheet = ActiveSheet
    Application.ScreenUpdating = False
    
    For Each Ws In Worksheets
        Ws.Activate
     'Remove formulas & external links
        With Cells
            .Select
            .Copy
            Selection.PasteSpecial Paste:=xlValues
            Application.CutCopyMode = False
        End With
     'Remove Hyperlinks
        Cells.Hyperlinks.Delete
        [A1].Select
    Next
    
    FirstSheet.Activate
End Sub
Bob G
you may not need to loop thru all the worksheets. In your case, you are working on all the cells in all the worksheets. You should be able to select them all at once and perform what you need.
Try this extra piece of code ****untested****
CODE
Set oApp = CreateObject("Excel.Application")
    oApp.Visible = False
    oApp.Workbooks.Open FileName:=strfilename
    oApp.UserControl = True
oApp.Application.DisplayAlerts = False
oApp.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
oApp.ActiveWindow.SelectedSheets.Delete
oApp.ActiveWorkbook.Sheets.Select
oApp.cells.hyperlinks.delete
classicxman
Thank you Bob. I can see how that would work for something like a hyperlink or a connection where the code can identify, select and delete. How would this look if I wanted to go through each sheet and replace formulas with values?
Bob G
off the top of my head and only for conversation purposes.
Is what you are describing basically selecting the entire worksheet, the selecting copy, then paste special ?
classicxman
Basically, yes.
Bob G
then it would work the same basic way. Selecting all the worksheets and then all the cells and copy and paste formulas.
You can record a macro that does the steps and see what the excel vba would look like
JonSmith
Can I ask what the overall process is (As in what/why you are exporting from Access. What is in the Excel file etc) as I think there are other ways of tackling this that would remove the need to delete sheets and links etc and could all be done Access side.
Bob G
@jon,
The way I read this was that the OP wanted to move the "work" from Excel to Access.
Bob
JonSmith
Hi Bob, I think I am reading it the other way, Access work to Excel. But the reason I am after a little bit more info is because I think this is the same thing that NineIron is asking about in another topic right now and I think some of the solutions proposed there (Pushing the data to a pre-defined spreadsheet avoiding links and MS-Query so as to allow emailing) might be better options then what is happening here. Need a little more detail to be sure though, I could be barking up the wrong tree.
Bob G
will have to wait to see which way it really is.
classicxman
Firstly, thanks for all your replies. I have something that is 98% there now, but let me try and better explain what I am doing.
) I have a lot of databases and queries built in Access than run every day through a master batch file. These are pulling in fresh data from a variety of sources.
2) I have linked data tables (from Access tables) in Excel files to create Dashboards with graphs, statistics and other relevant information that is emailed to a distribution. These Excel files contain a lot of pivot tables, vlookups, array formulas, etc., to provide the "graphs, statistics and other relevant information" I need.
3) Rather than emailing out a 50mb Excel file, I wanted convert all formulas to values, delete the sheets with linked tables that I don't need anymore, and create a slimmed down email version of the Excel file.
4) I already had the Excel macros to do this, but I wanted to run it from my Access master batch file directly--hence, the crux of my biscuit (problem, as it were)
This was my original Excel code which worked great:
CODE
Sub DeleteLinks()
    Dim Ws As Worksheet, FirstSheet As Worksheet
    Set FirstSheet = ActiveSheet
    Application.ScreenUpdating = False
    For Each Ws In Worksheets
        Ws.Activate
     'Remove formulas & external links
        With Cells
            .Select
            .Copy
            Selection.PasteSpecial Paste:=xlValues
            Application.CutCopyMode = False
        End With
        [A1].Select
    Next
    FirstSheet.Activate
End Sub

This is where I am at with the Access version. The only problem now, is that it is not fully releasing the Excel file as I cannot open it until I quit Access. What am I still doing wrong?
CODE
Function DeleteLinks()
Dim xlApp As Excel.Application
Dim Ws As Worksheet, FirstSheet As Worksheet
Set xlApp = CreateObject("Excel.Application")
Set FirstSheet = ActiveSheet
With xlApp.Application
    .Visible = True
    .ScreenUpdating = False
    .Workbooks.Open "C:\Queries\Coating_Editing\test4.xlsx"
        For Each Ws In Worksheets
            Ws.Activate
    'Remove formulas & external links
            With Ws.Cells
                .Select
                .Copy
                .PasteSpecial Paste:=xlPasteValues
                xlApp.Application.CutCopyMode = False
            End With
            Ws.[A1].Select
        Next
    .Sheets("Summary").Activate
    'Save
    .ActiveWorkbook.Save
    .DisplayAlerts = False
    .ActiveWorkbook.Close SaveChanges:=True
    .Application.Quit
End With
Set xlApp = Nothing
End Function
Bob G
this should be what you need.
xlApp.Close
set xlApp = Nothing
classicxman
Thanks Bob!
classicxman
While the code is working now, Jon's comment above got me thinking that there is probably a more efficient way to do what I am doing. Instead of creating a linked data table in Excel which pulls from my Access table and refreshing each time, have Access push the data into Excel eliminating the links. If I have an Excel file called "C:\Dashboard.xlsx" with a worksheet called "Data", what is the best way to transfer all records from my Access table "FinalDataTable" into my Excel worksheet "Data"? I would need the code to overwrite existing records in "Data" without any prompts. Can you point me in the right direction? Thanks.
Bob G
i think if you create a new thread for this new scenario you will get more people looking to give a good answer. You can also search UA
JonSmith
Hi Classicxman,
See attached my example of pushing data to an existing Excel file. I choose to use an Excel template file when doing this as it opens a new instance of the file and forces the user to save a new copy somewhere keeping the original template always intact.
You need to set a Reference to the 'Microsoft Office Excel Object Library' in the Access file otherwise it won't run. I used a dynamic named range in Excel as the source data for my Pivot Tables and Charts, this means that now matter how much data you add in it will always include all of that in them and I have written some quick code on each one so they refresh each time you select them.
Apart from that it should be self-explanatory from the files.
Good luck if you choose to go this way and don't hesitate to ask for any more help.
JonSmith
classicxman
Jon--that is great. Thank you. I'll give it a try.
classicxman
Jon--I noticed you have the range set to 65,536 rows. Will this same procedure work if I have 250,000+ records to transfer?
et rng = wks.Range("A2:G65536")
JonSmith
Hi Classicxman,
That was the row limit in Excel 2003, I have just tested changing that to row 5 on my example and seeing if it still runs. I still adds all the records (which go way past 5) so I don't think that number is particularly important from a last row perspective. As long as Excel has enough rows it will add them all (from what I can tell, test it and see how it goes).
JS
classicxman
Jon or anyone else....
This code works great, except I have a few numeric flag fields in my table that contain ones and zeroes. When the table is copied to Excel, they are converted into dates (1/0/1900 and 1/1/1900). I've tried formating the corresponding columns in the Excel template file as numbers, but still doesn't work. Any ideas?
CODE
Function pte()
On Error GoTo MyError
Dim rst As DAO.Recordset
Dim customQuery As String
Dim cnt As Integer
Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim rng As Excel.Range
Dim fileName As String
Dim fld As DAO.Field
    Set appExcel = Excel.Application
    '
    Set wbk = appExcel.Workbooks.Add(Application.CurrentProject.Path & "\testPTE.xltx")
    Set wks = wbk.Worksheets(1)
    Set rng = wks.Range("A2:R200001")
    
    Set rst = CurrentDb.OpenRecordset("EditMaster")
    If (rst.RecordCount > 0) Then
        cnt = 1
        For Each fld In rst.Fields
            wks.Cells(1, cnt).Value = fld.Name
            cnt = cnt + 1
        Next fld
        Call rng.CopyFromRecordset(rst, 200000, 18)
        wks.Columns.AutoFit
    End If
TidyUp:
    rst.Close
    Set rst = Nothing
    appExcel.Visible = True
    Set appExcel = Nothing
    
Exit Function
MyError:
    MsgBox "An error has occured", vbCritical
    'Enter any error loggin stuff here
    GoTo TidyUp
End Function
Bob G
when i put zero or one in a worksheet and then format as date I get the dates you specify. I change the format and it reverts back to the number. What is the actual value that shows in your cell without formatting ?
classicxman
The cell value shows as 1/0/1900
classicxman
I tried putting this into the code but I'm not sure it's correct. It doesn't seem to help.
!--c1-->
CODE
wks.Columns(Array("Leak_2", "RefWk_2", "P1_2", "P7_2", "P30_2", "P90_2", "P360_2")).NumberFormat = "0"
classicxman
Got it!!! I didn't realize this would work, but I just went into Excel and recorded the macro for what I wanted to do, then pasted the code into my Access code and voila!
!--c1-->
CODE
        Range("F:F,L:L,N:R").Select
        Range("Table1[[#Headers],[P1_2]]").Activate
        Selection.NumberFormat = "General"
        Application.Goto Reference:="R1C1"
JonSmith
Glad to hear you are happy with the code and were able to work out your formatting problem. Just fyi you don't need the select lines, you can just set them directly, eg
CODE
Range("A1).NumberFormat = "General"
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.