VBA Code to Call Excel Macro


I have a VBA code written to call an Excel macro. The code runs, and it runs the Excel macro just fine, but for some reason in Access the code is erroring on the Excel macro even though it ran. Not sure what is happening. It is erroring on the red line of code. I did not write the Excel code, but it doesn't seem to be the issue. Any ideas what would make this line error even though the Excel macro runs fine?

Access Code:

	Code:
	Function QMFQueries()
Dim xl As Object
'Step 1:  Start Excel, then open the target workbook.
    Set xl = CreateObject("Excel.Application")
    xl.Workbooks.Open ("C:REPORT_DATAChronicUnitsBuildSheetsGet QMF Data.xlsb")
'Step 2:  Make Excel visible
    xl.Visible = True
'Step 3:  Run the target macro
   xl.Run "Get_QMF_Data_Nationals"
'Step 4:  Close and save the workbook, then close Excel
    xl.ActiveWorkbook.Close (True)
    xl.Quit
'Step 5:  Memory Clean up.
    Set xl = Nothing
End Function

Excel Code:

	Code:
	Sub Get_QMF_Data_Nationals()
   Application.EnableEvents = False
   Application.DisplayAlerts = False
   Dim BeginTime As Double
   BeginTime = Timer
   Calculate
   Sheets("NATIONALS").Shapes("Autoshape 101").Visible = False
   Sheets("NATIONALS").Shapes("Autoshape 102").Visible = True
   Application.ScreenUpdating = True
   Set QMFWin = CreateObject("QMFWin.Interface")
   Stat = QMFWin.InitializeServer("eProd", "", "", False)
   If Stat  0 Then
   Sheets("NATIONALS").Shapes("Autoshape 101").Visible = True
   Sheets("NATIONALS").Shapes("Autoshape 102").Visible = False
   [HOME1].Activate
       MsgBox ("Unable to Initialize QMF Server.  " + QMFWin.GetLastErrorstring() + "     ")
       Set QMFWin = Nothing
       Application.EnableEvents = True
       End
   End If
   cnt1 = 0
   rwcnt1 = 0
   Do
   starttime = Timer
   procname1 = Cells([PROC_NAME1_1].Row + rwcnt1, [PROC_NAME1_1].Column)
   runproc1 = UCase(Cells([PROC_NAME1_1].Row + rwcnt1, [PROC_NAME1_1].Column - 1))
   If runproc1  "YES" And runproc1  "Y" Or procname1 = "" Then GoTo 1
   Cells([PROC_NAME1_1].Row + rwcnt1, [PROC_NAME1_1].Column).Interior.Color = 65535
   Cells([PROC_NAME1_1].Row + rwcnt1, [PROC_NAME1_1].Column + 1).Interior.Color = 65535
 
   ProcID = QMFWin.InitializeProc(2, procname1)
   If ProcID < 0 Then
   Sheets("NATIONALS").Shapes("Autoshape 101").Visible = True
   Sheets("NATIONALS").Shapes("Autoshape 102").Visible = False
       MsgBox ("Unable to Initalize Proc.  " + QMFWin.GetLastErrorstring() + Chr(10) _
       + "Check the file name and make sure that it is in the path specified.")
       Set QMFWin = Nothing
       Sheets("NATIONALS").Shapes("Autoshape 101").Visible = True
       Sheets("NATIONALS").Shapes("Autoshape 102").Visible = False
       [HOME1].Activate
       ActiveWorkbook.Save
       Application.EnableEvents = True
       End
   End If
 
   Stat = QMFWin.RunProc(ProcID)
   If Stat  0 Then
       Application.WindowState = xlMaximized
       MsgBox (QMFWin.GetLastErrorstring() + Chr(10) + "A window will appear showing the procedure called and the date range global variables used." + Chr(10) _
       + "Please check the date range parameters and if correct, debug the proc and/or any queries using QMF for windows then try again." + Chr(10) _
       + "This error may also have been caused by an application disconnect with the server.")
       QMFProcInfo.Label1.Caption = "GLOBAL DATE RANGE VARIABLES"
       QMFProcInfo.Label2.Caption = procname1
       QMFProcInfo.TextBox1.Text = ("&FROM = " + QMFWin.Getglobalvariable("FROM") + Chr(10) + "&TO  = " + QMFWin.Getglobalvariable("TO"))
       QMFProcInfo.TextBox2.Text = (QMFWin.GetProcText(ProcID))
       QMFProcInfo.Show
       Application.WindowState = xlMaximized
       Sheets("NATIONALS").Shapes("Autoshape 101").Visible = True
       Sheets("NATIONALS").Shapes("Autoshape 102").Visible = False
       [HOME1].Activate
       ActiveWorkbook.Save
       Application.EnableEvents = True
       End
   End If
 
   Cells([PROC_NAME1_1].Row + rwcnt1, [PROC_NAME1_1].Column).Interior.Color = 5296274
   Cells([PROC_NAME1_1].Row + rwcnt1, [PROC_NAME1_1].Column + 1).Interior.Color = 5296274
   Cells([PROC_NAME1_1].Row + rwcnt1, [PROC_NAME1_1].Column + 1) = Date & " " & Time
   Cells([PROC_NAME1_1].Row + rwcnt1, [PROC_NAME1_1].Column + 1).HorizontalAlignment = xlCenter
   Cells([PROC_NAME1_1].Row + rwcnt1, [PROC_NAME1_1].Column + 2) = Format(DateAdd("s", (Timer - starttime), "00:00:00"), "h:mm:ss")
   cnt1 = cnt1 + 1
1:  rwcnt1 = rwcnt1 + 1
   Loop Until rwcnt1 > 30
   Set QMFWin = Nothing
Dim hours1 As String, minutes1 As String, seconds1 As String
   hours1 = Format(DateAdd("s", (Timer - BeginTime), "00:00:00"), "H")
   If hours1 = 0 Then hours1 = "" Else If hours1 = 1 Then hours1 = hours1 & " HOUR, " Else hours1 = hours1 & " HOURS, "
   minutes1 = Format(DateAdd("s", (Timer - BeginTime), "00:00:00"), "N")
   If minutes1 = 0 Then minutes1 = "" Else If minutes1 = 1 Then minutes1 = minutes1 & " MINUTE, " Else minutes1 = minutes1 & " MINUTES, "
   seconds1 = Format(DateAdd("s", (Timer - BeginTime), "00:00:00"), "S")
   If seconds1 = 1 Then seconds1 = seconds1 & " SECOND" Else seconds1 = seconds1 & " SECONDS"
   ActiveWorkbook.Save
   Application.WindowState = xlMaximized
   Application.EnableEvents = True
   Application.DisplayAlerts = True
   Sheets("NATIONALS").Shapes("Autoshape 101").Visible = True
   Sheets("NATIONALS").Shapes("Autoshape 102").Visible = False
   [HOME1].Activate
   Call ActivateAccess
   End
 
2:  MsgBox ("Can't continue.  Unable to locate the required files to update this report.")
3:  Sheets("NATIONALS").Shapes("Autoshape 101").Visible = True
   Sheets("NATIONALS").Shapes("Autoshape 102").Visible = False
   Application.WindowState = xlMaximized
   Application.EnableEvents = True
   Application.DisplayAlerts = True
   [HOME1].Activate
   ActiveWorkbook.Close savechanges:=True
End Sub




Sponsored Links:



How to write VBA code to import excel files into a table in Access with a click of a button? The excel file and access table has same column name.




Hi!

I've been rummaging about online and trying to solve this for hours now and I've finally given up in the hope someone more endowed with brain cells can help me out..

I'm working in Access, and I have a few reports that I run and that I've been copying and pasting into Excel and then doing some more work on. It occurred to me that I could write a macro to do all this for me, so I've made one in Access, and one in Excel to do the copying sheets etc, but the problem is in passing from one to the other. My Access module runs fine (after a lot of debugging) as does my Excel one, but I want Access to call the Excel macro at the end and hand over to it to finish the work. I put this part into a separate sub so I could test it without running the reports etc every time and this is my code:

Public Sub RunExcelMacro()

Dim objExcel As Object
Dim objWorkBook As Object

Set objExcel = CreateObject("Excel.Application")
Set objWorkBook = objExcel.Workbooks.Open("H:IT DepartmentGeneralReportingSeason Ticket AnalysisSeason Ticket Analysis - Data.xlsm")
objExcel.Visible = True
'objExcel.DisplayAlerts = False

objExcel.Run "SeasonTicketAnalysis"

End Sub

But I get this error.

Run-time error '1004': Cannot run the macro 'SeasonTicketAnalysis'. The macro may not be available in this workbook or all macros may be disabled.

Does anyone have any idea what the issue might be? It seems like an (unusually) helpful error message but I've checked that access to the Excel VB module is trusted (it is), that the name of the macro is spelled exactly as above (it is) and that the macro runs fine in Excel (it does), and that macro security isn't set to high (its set to the not recommended very low run all macros level), and after that I've run out of ideas. The macro is within the xlsm workbook this code opens, not in Personal.

Thank you very much in advance for any help..




Hi folks,

I'm in need of some VBA code to do the following from a Microsoft Access Table (version 2007).
The idea is to attach the code to a macro in Access and attach to a button on a Access form.
I need to create/export two Excel spreadsheets from the table named "Main" and place them to a specified
location (For Example: C:my exports). I've done transfer to spreadsheet in the past but in this case I need to do some specific checks and manipulation so help is greatly appreciated!

Table 'Main' has 6 columns (No Primary Keys):
Section: Text
Page #: Text
Item: Text: Text
New_Page #: Text
Page_Sort: Number
Delete: (Check Box)

Spreadsheet #1:
Needs to look like this:
Item (Column A) Pages (Column B)
Give Kids a Smile 9
Concentrix Handpieces New! 468
430-Series Handpieces 468
BURS Specialty 52, 54_55, 70_71
BURS 49_127
BURS Carbide 49_79

It needs to first evaluate the 'Delete' field (Check Box) and ignore any records with a check.
It then needs to evaluate the 'New_Page #' field and ignore any blank records.
It then needs to evaluate where the 'New_Page #" field is blank (or null) and the 'Delete' field is ALSO blank and
if that scenario is true, then a 'Msg Box' should appear warning the user "Files Not Complete, Do you want to proceed?"
(FYI - I will have a report for them to view those instances outside of this process)
If they choose to proceed then:

Basically it needs to take all records for given 'Item' and truncate the data in the 'New_Page #' field and
comma seperate them.

Spreadsheet #2:
Needs to look like this:
Section (Column A) Page Numbers (Column B)
COVER, STORY, & INTRO 1-17
Total Health 18-24
Acrylics 25-30
Alloys 31-35
Anesthetics 36-44
Articulating 45-48
Burs 49-127
CAD/CAM 128-138
Cements & Liners 139-159

The evaluation on this one is a little tricker as the related fields are text as opposed to a number.
It basically needs to search the 'New_Page #" field for the smallest number (thought text...as some records could have underscores)
and search for the largest number and add the "-" (Hyphen) between them.

This process also needs to ignore any records with a check in the 'Delete' column.
This process should also ignore any records with a blank (or null) in the 'New_Page #' field.

HELP!
I hope I gave enough info but if I missed anything please ask and I will gladly answer and I appreciate all the help!
Sample of what table "Main" looks like:

Section Page # Item New_Page # Page_Sort Delete COVER, STORY, & INTRO 9 Give Kids a Smile 10 9 0 COVER, STORY, & INTRO 12_15 What’s NEW!
12 -1 Total Health 18_24 TOTAL HEALTH New!
18 0 Total Health 19 VELscope Vx
19 0 Total Health 20_22 Sleep Complete New!
20 0 Total Health 23 Microlux DL
23 0 Total Health 23 DNA Testing
23 0 Total Health 23 Salivary DNA Tests
23 0 Total Health 24 OralDNA
24 0 Total Health 24 OraRisk HPV Salivary DNA Test
24 0 Total Health 24 MyPerioPath Salivary DNA Test
24 0 Acrylics 25 Coe Tray Plastic
25 0 Acrylics 25 Fastray
25 0 Acrylics 25 Sapphire Impression Material
25 0 Acrylics 25 Easy Tray
25 0 Acrylics 25_30 ACRYLICS & RELINE MATERIALS
25 0 Acrylics 25 Rimseal
25 0 Acrylics 25 Hydroplastic
25 0 Acrylics 25 Jet_Tray
25 0 Acrylics 26 Hydro_Cast
26 0 Acrylics 26 Paladon Ultra New!
26 0 Acrylics 26 Tissue Conditioner
26 0 Acrylics 27_28 Hard Reline Materials
27 0 Acrylics 27 Hygenic Perm
27 0 Acrylics 27 Chairside Reline Material
27 0 Acrylics 27 Ufi Gel Hard C
27 0 Acrylics 27 Coe Rect
27 0 Acrylics 27_29 Reline Materials
27 0 Acrylics 28 Dentusil Denture Reline
28 0 Acrylics 28 Silk Line
28 0 Acrylics 28_29 Soft Reline Materials
28 0 Acrylics 28 Truliner
28 0 Acrylics 29 Sofreliner
29 0 Acrylics 29 Acraweld Repair Material
29 0 Acrylics 29 Z_Bur
29 0 Acrylics 29 Versa_Soft
29 0 Acrylics 29_30 Repair Materials
29 0 Acrylics 29 Trusoft
29 0 Acrylics 29_30 Denture Repair Materials
29 0 Acrylics 30 DuraLay
30 0 Acrylics 30 Dura Seal
30 0 Burs 49_127 BURS 53_128 49 0 Burs 52 BURS Specialty 50 52 0 Burs 54_55 BURS Specialty 68_78 54 0 Burs 70_71 BURS Specialty 88_98 70 0 Equipment- Small 368 Microetcher
368 -1




Hi,

Have used the access forum but this is my first foray into excel! I am hoping that someone maybe able to assist with the following query:

I have a pivot table that has two columns and 29 rows. The essential function that i'd like the VBA code to perform is to open each row to a new worksheet and the for each (29) worksheets to be copied and transferred into new workbooks (the workbooks would then be saved a name contained in the cell). I have been able to write a code that performs this function but i cannot get it down the pivot table repeating this. Code attached below:
PHP Code:
Sub Macro1()

'
' Macro1 Macro
Dim Cell As Range
Dim b As Integer
Dim a As String
Dim d As String
Dim ws As Worksheet


Range("B4").Select
Selection.ShowDetail = True
ActiveSheet.Name = Range("D2").Text
Cells.Select
Selection.Copy
ActiveSheet.Name = Range("D2").Text
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
ActiveSheet.Name = Range("D2").Text
Cells.Select
    Cells.EntireColumn.AutoFit
    ActiveWindow.Zoom = 85
    Selection.RowHeight = 14.25
    Cells.EntireColumn.AutoFit
Sheets("Sheet2").Select
    ActiveWindow.SelectedSheets.Delete
Sheets("Sheet3").Select
    ActiveWindow.SelectedSheets.Delete
Columns("E:E").Select
Selection.Delete Shift:=xlToLeft
Columns("E:E").Select
Selection.Delete Shift:=xlToLeft
Columns("F:F").Select
Selection.Delete Shift:=xlToLeft
ActiveWorkbook.SaveAs Filename:="J:" & Range("D2")
ActiveWorkbook.Close
End Sub 




Hi guys,
Earlier I had a excel file with many columns.
I wrote a code in Excel VBA to fill up the last two columns according to the data in the other columns of the row.
It worked fine.
Now, the whole thing is transferred to an Access table.

Pls help me convert this Excel VBA code to Access VBA.

Code: Sub GeneratePorEngID() Range("S2:T5000").Clear Dim PrevTaskID As Integer Dim intNO As Long Dim intCount As Integer intNO = 1 PrevTaskID = 1 For i = 2 To 5000 'If PrevTaskID = 1 Then intNO = 1 If Cells(i, "J") "" Then If PrevTaskID = Cells(i, "J") Then Cells(i, "S") = intNO intNO = intNO + 1 intCount = intCount + 1 Else For j = i - intCount - 1 To i - 1 Cells(j, "T") = intNO intNO = intNO + 1 Next Cells(i, "S") = intNO intNO = intNO + 1 intCount = 0 PrevTaskID = Cells(i, "J") End If End If Next End Sub Thanks a ton in advance




I am having a problem trying to find VBA code to import an excel 2007 file into my access 2007 dao database. My problem is that with each excel record I need to create 2 different records which will be written into different tables in my database.




I have a button on a form in an Access 2007 DB that opens a large Excel sheet with some simple VBA code. I would like to add to the VBA code so it will search the worksheet after it opens for a cell that matches a value passed from a form control (text box), and then goes to that cell in the visible window. Essentially just using the VBA code to execute the Find function. I don't have a lot of experience with VBA and the Excel objects. Any help would be greatly appreciated!




Trying to get vba code to enter in repetive data.

Basically there is two tables that this deals with, Chemical and Chemical Details Table.

Chemical has this field called Method, which determines what chemicals are tested, hence the parameters field in chemical details, which has a record for each chemical.

Since the methods always use the same chemicals, i want the vba code to generate the records with the paramaters automatically depending on which method is chosen.

The problem is getting the data entered in the ChemDetails tables so it corresponds with Chemical Table, meaning the primary key doesn't match up, Chemical_DataID, which is a autonumber.

I tried setting the Chemical_DataID in the details table to what the Chemical table would have, but that doesn't work, it doesn't seem to let u change the number.

Any Ideas?
Thanks Greg

Private Sub Method_AfterUpdate()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim tempID As Long
Dim rstDet As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Chemical", dbOpenDynaset)
Set rstD = dbs.OpenRecordset("Chemical_Details", dbOpenDynaset)

rst.MoveLast
rst.AddNew
tempID = rst![Chemical_DataID] - 1
If rst![Method] = "EPA 602" Then
rst.MoveLast
rstD.AddNew
rstD![Chemical_DataID] = tempID
rstD!Parameter = "t butylmethylether"
rstD.Update
rstD.AddNew
rstD!Parameter = "Benzene"
rstD![Chemical_DataID] = tempID
rstD.Update
rstD.AddNew
rstD!Parameter = "Toluene"
rstD![Chemical_DataID] = tempID
rstD.Update
rstD.AddNew
rstD!Parameter = "Ethylbenzene"
rstD![Chemical_DataID] = tempID
rstD.Update
rstD.AddNew
rstD!Parameter = "m + p Xylene"
rstD![Chemical_DataID] = tempID
rstD.Update
rstD.AddNew
rstD!Parameter = "o-Xylene"
rstD![Chemical_DataID] = tempID
rstD.Update
End If
End Sub




Hi,

Would anyone please be able to help me with some VBA code to repetitively clear the contents of a cell range in an excel worksheet subject to some values in other cells of the same worksheet ?

In cell E2 I have two possible values, "In Play" or "Not In Play" (quotations marks only for illustation, not actually part of value) and in cell F2 I have one possible value, "Suspended".

In column T for cells T5 to T25 I can have 4 possible values, "PLACED", "PENDING", "CANCELLED" or a numerical value.

The worksheet is constantly refreshed from an external data source and I hope to be able to use something like an event property or similar that will react to the sheet refreshing or recalculating to initiate the VBA code.

If the cell contents in range T5 to T25 contain any value other that "PENDING" then I wish to clear the cell contents if E2 = "Not In Play" and F2 "Suspended".

If F2 = "Suspended" or "E2" = "In Play" I do not wish the cell contents in the range T5 to T25 to be cleared.

I think that I need some type of nested IF statement but just do not know where to start. I can open the VBA editor and paste the code into the specific worksheet but I am stuck with the code.


Thx & Rgds
Growlos




Is it possible to utilize the code from an Excel macro as the OnClick() code for a command button in an Access form?

If not, is there a way to adapt the macro code somehow?




Hi,

Does anyone know the VBA code to bring up the "search and replace" window in access 2007?

something like...

docmd.search and replace?

comments/thougth much appreciated




I'm using Access 2003.

Sorry if this is a dumb question, but is there VBA code to detect whether a given form is already open? (I've googled for a while, but I can't seem to find the answer.)

Thanks.

Wayne




I do not know VBA syntax or how to properly set up programming using VBA code in MS Access.

I would like to automate changes to two records in the database depending on the current date. The situation is that I have two records for one person (each with a different primary auto number field). Each of the two records has a different address for mailing purposes. There is a winter residence and a summer residence. When I prepare a mailing list, I archive the inactive address, depending on the current date, and the active address only is included on the mailing list.

So I would like the VBA code to do the following:

;;REM summer address
If membershipID=12345 ((if date > 04/30/2012 and date < 12/01/2012) set Archived=false, else set Archived=true)

;;REM winter address
If membershipID=67890 ((if date > 04/30/2012 and date < 12/01/2012) set Archived=true, else set Archived=false)


Can the date be used with MMDD only without YYYY so that the code would not have to be edited each year?

Also, how do I enter the VBA code into the database.
--------------------------------------------------------------
NOTE: I already have some unrelated code in this database. The code was written for me with instructions for its insertion, a few years ago. At this point, I don't remember the steps taken to enter the code into the database. But in this case, the code is linked to a form and only activates if I add or edit a record.

It is shown as a Microsoft Office Access Class Objects/Form frmMembership.
It inserts the current date into the AddDate field when a new record is created. The EditDate field has the current date inserted when there is an edit to an existing record.
this is the unrelated code that already exists in this database:

Option Compare Database

Option Explicit

Private Sub AddDate_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub EditDate_AfterUpdate()

End Sub

Private Sub EditDate_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.Dirty Then
Me![EditDate] = Date
End If

End Sub

------------------------------------------------------------------------------------------------


However, in both cases, I manually have to post something in a record for the VBA codes to activate.
What I want now, if for the two records to automatically update the Archived field whenever the database is opened -- that is, I don't want to have to remember to do anything to the two records based on the date. I want VBA code to have the database do this automatically whenever the database is opened.

Thanks.

Allan




Hello Everyone,

I know it can be done and I have seen some code snippets before, but how can I use Access vba code to count all forms, tables, queries, reports, vba modules, objects to get a total count of each for an Access database?

Any help is greatly appreciated.

Thanks,

Kurt




I would like to convert this line of VBA code to VB code. My first stumbling block is that VB does not seem to like docmd. I get an error message saying object required.
objects could be form1, data1 or dbgrid 1 but typing in docmd. does not return a list of possibles so I assume VB does not like docmd

The line prints I hope a filtered database. It works in Access. The line of code is
DoCmd.PrintOut acPrintAll, 1, 1, acHigh, 1, True

Smiler44




How to trough VBA code to create dynamic table in MS Access 2003? Any sample code is welcome. TQ




I installed the RDMail add-in from Ron de Bruin into my Excel program. This allows the emailing of PDF pages to Outlook receipients. I don't know VBA code, but I was able to modify it to eliminate the need for a person to click on the info box. If I can make this code into a macro, then I could use the task scheduler on the server to daily, open the excel workbook, and execute the macro.

I don't know how to take the VBA code and make it into a macro. Your help would be greatly appreciated!




I am comfortable with VBA and/or creating macros. I have an Access front end that users interact with. I have stripped away the database window so the only visual thing is the switchboard from which users access menu items.

Problem is this. For some reason when we upgraded to Access 2007, we are finding that the switchboard locks up when data is imported or exported via a programmed vba module. So, user is in a section and performs a task by clicking a menu item. That task imports or exports data according to a vba module, and then user can't exit that portion of the switchboard. The switchboard becomes totally unresponsive.

The only fix for users is to close the Access front end and then reopen it (obviously less than optimal). If I am in the room, I can unhide the database window, and then close the switchboard and reopen it which also solves the problem.

Anyone have any ideas about how to fix this? My easiest solution I thought of was to create a quick Macro or VBA module that would close the switchboard form and then reopen it. Problem is I need a way to call that code with hot keys or something....

Anyone have any ideas?

Thanks,

Ben




Hi

Any ideas how to access macro modules using VBA, the macros run queries and other macros and I have quite a lot. Instead of opening each one and checking for different queries and what is in each I want to do it in code. I have done similar with my queries and tables using:

For Each tdf in db.tablesdefs

For each qdf in querydefs

But what is it you use for macros?

Reason i am doing this is because I am copying a DB and using it for other sub sites where all I need to do is change some built in names in queries and tables, and so need to do the same with query names that are in macros.

Thanks

G