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



Post your answer or comment

comments powered by Disqus
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

Hi,

New to the board.

I am current writing some VB code for some Excel macros for work, but I have never learnt VB so I am picking it up as I go along.

I am trying to copy rows of a spreadsheet to other spreadsheets based on a cell condition.

I know the current code I have...

If Cells(x, 5).Value = "8" Then
Cells(x, 1).EntireRow.Copy
Sheets("Sheet1").Cells(a, 1).PasteSpecial
a = a + 1
End If

...works to copy rows to another sheet in the same spreadsheet, but how would I copy to Sheet1 of another spreadsheet, called "Test.xls", say?

Any help would be appreciated.

Cheers
Nick

Hi Gents,

I have 2 tables with one-to-many relationship

T_RPO_Header (Eno (number) Primarykey, Ename (Text))
T_RPO_Footer(Eno (number), RPO_Number (Text), some other fields)

Field Eno is tied with one-to-many relationship.

I am fetching data from Access a single table to Excel. It works fine with following code line.


'Other Code lines here to open Excel Application
--
--
--
Set rst = CurrentDb.OpenRecordset("SELECT ENO, RPO_No, FROM T_RPO_Footer;")
--
--
--
But I am confused what could be the code line incase I need to pull the data from 2 tables which are tied with one-to-many relationship.

For example I need pull Ename also from Header table in the above code line. How can I do that?

One more thing I need to have clarification that I put all the above lengthy VBA code on Click Event of a button. The above code produces ONE report only. I need to have 14 reports so if I do write similar code lines for each reports, would the form be very heavy while loading?

And even for one single report it takes long time since record set is of 25 columns..

I then decided to put all the above code in a module / modules and just call it from the same event of button. This will help to bring my form to light weight.

I was under impression that module works faster than VBA codes written behind the form. But it takes similar time.

With kind regards,

Ashfaque

All,

I am attempting to write a utility to analyze a specified Access database to determine what objects are used and what is unused. For example. Test every table, for use in any query SQL, form, or VBA code in modules, etc...

Once completed, it will produce a list of all objects that are not used somewhere in the dB.

This will allow me to clean up several old process dBs. With out having to manually review each part of the dB.

Well, enough back ground. Here is my problem.

I want to analyze the built in MS Access Macros to see what Queries, Tables, VBA Code.... is called by each macro.

If I can access the steps in the macro via code I could pass a simple text string and validate whether or not the object is used in any macro.

Here is a sample of my code for checking for a table in all queries.

In the function below I pass the object name, and object type for reporting.
The code will capture the queries, where and object is used, or will capture the object as not being used after testing all queries.

--BEGIN CODE--
Function TestForTblOrQryInSQL(strTblQrySearch As String, strObjType As String) As String

Dim qry1 As QueryDef
Dim blnTblFoundInAnyQry As Boolean
Dim blnTblFoundInCurrQry As Boolean
Dim intQryLoop As Integer
Dim rst2 As DAO.Recordset

blnTblFoundInAnyQry = False
For Each qry1 In CurrentDb.QueryDefs
blnTblFoundInCurrQry = False
If InStr(1, qry1.SQL, strTblQrySearch) 0 Then
intQryLoop = 1
Do Until intQryLoop > 6
Select Case intQryLoop
Case 1
If InStr(1, qry1.SQL, " " & strTblQrySearch & " ") 0 Then
'Record Table and Qry name
blnTblFoundInCurrQry = True
End If
Case 2
If InStr(1, qry1.SQL, " " & strTblQrySearch & Chr(13)) 0 Then
'Record Table and Qry name
blnTblFoundInCurrQry = True
End If
Case 3
If InStr(1, qry1.SQL, " " & strTblQrySearch & ";") 0 Then
'Record Table and Qry name
blnTblFoundInCurrQry = True
End If
Case 4
If InStr(1, qry1.SQL, " [" & strTblQrySearch & "] ") 0 Then
'Record Table and Qry name
blnTblFoundInCurrQry = True
End If
Case 5
If InStr(1, qry1.SQL, " [" & strTblQrySearch & "]" & Chr(13)) 0 Then
'Record Table and Qry name
blnTblFoundInCurrQry = True
End If
Case 6
If InStr(1, qry1.SQL, " [" & strTblQrySearch & "];") 0 Then
'Record Table and Qry name
blnTblFoundInCurrQry = True
End If
End Select
If blnTblFoundInCurrQry = True Then
intQryLoop = 7
Else
intQryLoop = intQryLoop + 1
End If
Loop
If blnTblFoundInCurrQry = True Then
'Record Table and Qry Name
Set rst2 = CurrentDb.OpenRecordset("db_Clean_Objs_Uses")
rst2.AddNew
rst2!ObjName = strTblQrySearch
rst2!ObjType = strObjType
rst2!ObjTypeUsed = "Qry"
rst2!ObjNameUsed = qry1.Name
rst2.Update
Set rst2 = Nothing
blnTblFoundInAnyQry = True
End If
End If
Next qry1
If blnTblFoundInAnyQry = False Then
Set rst2 = CurrentDb.OpenRecordset("db_Clean_Objs_No_Uses")
rst2.AddNew
rst2!ObjName = strTblQrySearch
rst2!ObjType = strObjType
rst2.Update
Set rst2 = Nothing
End If

End Function

--END CODE--

I am using MS Access 2002 (XP)

I hope this make sense.

Thanks in advance for any input.

Hi, I have an excel template that I use every day.. pretty much the same routine.. Update the date field, make the necessary changes, attach it to an email.

I thought it'd be nice to create a bit of VBA code to do the routine stuff, so I did.
I have added a button on my toolbar which has assigned the one macro that takes care of updating the date cell
I have another button that I click when I'm done with the document. This buttons is assigned another macro which grabs some information from the current worksheet, and fills in some information on the email... just ready for me to select a recipient.

All the macros and VBA code is stored in the template (.xlt) that I create each worksheet with. But here is the problem

When I click these buttons, Excel opens the last document I created and runs the macros off there rather than the template that I'm running.

Can anyone suggest a way to pull this off?

Thanks in advance

Afternoon Folks

Please can somebody help me with a problem.

I have an Access form with a command button - the button runs a access macro which uses the transfer spreadsheet command to export a table into an excel workbook. All this works fine but I would like to apply some formatting to the spreadsheet.

I have recorded a macro within excel which applies the formatting but not sure how to piece the two together. I would like to combine the functions to opening Excel, opening the sheet and then run the formatting macro. As this is a new excel sheet, excel doesnt save my macro. The only way i can get this to run is to open another Excel workbook which has my macro saved and run it from there.

Below is the code I have used from my access module:

/code
Sub OpenSpecific_xlFile()
' Late Binding (Needs no reference set)
Dim oXL As Object
Dim oExcel As Object
Dim sFullPath As String
Dim sPath As String


' Create a new Excel instance
Set oXL = CreateObject("Excel.Application")


' Only XL 97 supports UserControl Property
On Error Resume Next
oXL.UserControl = True
On Error GoTo 0


' Full path of excel file to open
On Error GoTo ErrHandle
sFullPath = "C:CLACLA_Pupils"


' Open it
With oXL
.Visible = True
.Workbooks.Open (sFullPath)
End With


ErrExit:
Set oXL = Nothing
Exit Sub

ErrHandle:
oXL.Visible = False
MsgBox Err.Description
GoTo ErrExit
End Sub
code



And here is the code behind my access form button:

/code
Private Sub Command59_Click()
On Error GoTo Err_Command59_Click
Dim stDocName As String
Dim oApp As Object
stDocName = "CLA Pupil Export"
DoCmd.RunMacro stDocName

Call OpenSpecific_xlFile

Exit_Command59_Click:
Exit Sub
Err_Command59_Click:
MsgBox Err.Description
Resume Exit_Command59_Click
End Sub
code

Also here is my code for my excel macro:

/code
Cells.Select
Selection.Columns.AutoFit
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Application.Goto Reference:="R1C1"
ActiveCell.FormulaR1C1 = "Original Pupil Data"
Range("A2").Select
Application.Goto Reference:="R1C27"
ActiveCell.FormulaR1C1 = "PLASC"
Range("AA2").Select
Application.Goto Reference:="R1C49"
ActiveCell.FormulaR1C1 = "FFT Estimates"
Range("AW2").Select
Application.Goto Reference:="R1C134"
ActiveCell.FormulaR1C1 = "Keys To Success"
Range("ED2").Select
Application.Goto Reference:="R1C154"
ActiveCell.FormulaR1C1 = "QCI Data"
Range("EX2").Select
Application.Goto Reference:="R1C178"
ActiveCell.FormulaR1C1 = "Absence Data"
Range("FV2").Select
Application.Goto Reference:="R1C1:R1C26"
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Application.Goto Reference:="R1C27:R1C48"
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Application.Goto Reference:="R1C49:R1C133"
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Application.Goto Reference:="R1C134:R1C153"
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Application.Goto Reference:="R1C154:R1C177"
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Application.Goto Reference:="R1C178:R1C189"
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Rows("1:1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Selection.Font.Bold = True
Selection.RowHeight = 30
Application.Goto Reference:="R1C1"
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
End With
Range("AA1:AV1").Select
With Selection.Interior
.ColorIndex = 39
.Pattern = xlSolid
End With
Range("AW1:EC1").Select
With Selection.Interior
.ColorIndex = 39
.Pattern = xlSolid
End With
Range("AW1:EC1").Select
Selection.Interior.ColorIndex = 36
Range("ED1:EW1").Select
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
End With
Range("EX1:FU1").Select
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
Range("FV1:GG1").Select
With Selection.Interior
.ColorIndex = 45
.Pattern = xlSolid
End With
Rows("1:1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("AA1:AV1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("AW1:EC1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("ED1:EW1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("EX1:FU1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("FV1:GG1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("A1:Z1").Select
End Sub
/code

Any help would be greatly appreciated

Cheers
Daz


Not finding an answer? Try a Google search.