Modules & VBA :: How To Pass A String Containing Path Of Active Workbook
Mar 20, 2014
I want to pass a string aPath contains path of the active workbook to access vba module or sub. Below is my excel vba code that will open access db form. How do i pass the string value to access vba.
Code:
Dim aPath, aDbase, aDSource, aTable, exePath As String
Dim fileParam As String
aPath = ActiveWorkbook.Path
aDbase = "near_14.accdb"
aDSource = aPath & "" & aDbase
I am trying to write some code to open an existing excel form, populate it, and save it with a custom filename in a directory that is built using variables I have declared.
The only part I am having difficulty with is setting the pathname. Here is my code:
Code: 'Build path to save file to spath = "S:ContractsGeomatics LABSigned" & Company 'Build File name Filename = "" & reservationNumberforForm & " " & Company & "-Payment Schedule"
'Save the payment schedule to the S: drive 'xlApp.ActiveWorkbook.SaveAs Filename:="S:ContractsGeomatics LABSigned" & Filename, FileFormat:=xlOpenXMLWorkbook xlApp.ActiveWorkbook.SaveAs Filename:="" & spath & " " & Filename, FileFormat:=xlOpenXMLWorkbook xlApp.ActiveWorkbook.Saved = True
The code has fixed path information on a lot of places in different SQLs (DoCmd.RunSqL command). I want to replace fixed path info with variable path info. Variable path info is stored in the table.
I managed to achieve that in the following manner:
Code: Dim db As Database Dim dbName as String Set db = CurrentDb Set rs = db.OpenRecordset ("TableName", dbOpendynaset) rs.FindFirst ("ID = " & 2)
[Code] ....
where I would use as variable Function name instead of dbName.
How to make module that will enable to use Function name as variable path information for SQL queries?
With the OutputFile set to "" so the user can select the directory on where they want to store the exported template. I'm trying to figure out if I could get the file path and the file name and store it on a string so I could use it for something else.
I have one form with a calendar and i want to select the days in calendar and click in print button and the result is the list of day selected in calendar, printed.
Example:
I select the days 1, 2, 16 and 17 so the print result will be:
1 September 2014 2 September 2014 16 September 2014 17 September 2014
This is "my" vba code:
Code: Option Explicit Option Compare Database Const constShaded = 12632256 ' Shaded text box Const constUnshaded = 16777215 ' Unshaded text box Const constBackground = -2147483633 ' Background color for form (for unused textboxes)
[Code] .....
How i pass the checkboxs values to a string or table for print?
Hi, I have a command button that opens a pdf file. However i will be distributing the database as a runtime package and need to account for people having different versions of adobe reader so need to search for the filepath string where Acord32.exe is found. This is the code I have but I am stuck! Ay help aprpeciated!
Private Sub Command4_Click()
Dim stAppName As String Dim stPathName As String Dim fs As Object
Set fs = Application.FileSearch With fs .LookIn = "C:Program Files" .SearchSubFolders = True .FileName = "AcroRd32.exe" .Execute ????? stAppName = .FoundFiles Set fs = Nothing End With
'specify path name for version of adobe acrord32.exe 'stAppName = "C:Program FilesAdobeReader 8.0ReaderAcroRd32.exe " stPathName = GetIniSetting("C:WINDOWSSSI_DL3_PROGRS.ini", "DIR", "REPORT_FILELOCATION") & "HOOF.pdf" Shell stAppName & stPathName, vbMaximizedFocus
I have some vba code that exports the results of a query to a specific excel workbook. Code in this workbook then takes the exported data to "fill out" areas in the workbook. My staff will then use this workbook to perform analysis on a project. After they have completed the analysis, they will then save the excel workbook, and another person will then validate this excel and turn it into a PDF to send as an email attachment.
My question is, when my staff saves the excel workbook, is there a way they can save it directly to the access database? If not, would there be a way to use vba to "attach" this excel workbook to the appropriate record in the access db for future use?
I have a workbook which is already opened and I want to close it. the code that i have is (see below) is not working.
Code:
Function CloseExcel(sExcelFile As String) As Integer Dim XLapp As New Excel.Application Dim ObjXL As Excel.Workbook On Error GoTo ErrorTrap Set ObjXL = XLapp.Workbooks.Open(sExcelFile)
I have a excel object I have opened, and want to copy a query into a named range on the workbook.
heres the start have made
Sub openExcel() 'WillR - opens the specified Spreadsheet Dim xlApp As Excel.Application Dim xlWB As Excel.Workbook Set xlApp = New Excel.Application With xlApp
Now i want to write VBA code to check sheet name "Summary" in that workbook and check if any of column D value matches with Access table tblmain field "Reference" values. If yes then change the status field of tblmain to "Yes".
I would like to open an Excel workbook from MS Access and clear cell contents, or just delete some records in a specific worksheet.
If you open the test workbook, cell contents in RAW need to be deleted by calling from Access.
I have produced some code but it's partially working.
Code: Sub TestFileOpened() ' Test to see if the file is open. If IsFileOpen("test.xls") Then ' Display a message stating the file in use. MsgBox "File already in use!"
[Code] ....
If you put this in a standard module in access, the function works, but the part that doesn't work is where it says "activesheet". It somehow tries to recognize it as a variable, but it's not going to be a variable.
I have a question about the best way to go about linking an excel workbook to an ms access table? I have researched hyperlinks and attachments and ole objects and I am not sure what is the best option. I believe hyperlinks will be best, because the attachments will bloat the database, but I am not sure how to go about doing that in vba? My process is such:
1)Users enter project information 2)Users enter the sample information for each project 3)The project and sample information is combined by a query 4)The user clicks a button to print forms, which runs the query and opens an excel workbook template. 5)The data is exported to a specific sheet in the workbook 6)Using vba in excel, the data is used to populate cells in different sheets depending on critera.
After all of this is accomplished, I need the user to SAVE AS the excel workbook and it needs to be "attached" to the ms access 'project' table. I am not sure what is the best way to link this back to the database. I would like to automate it with vba if possible.
I have created a form that will print the active report. I have managed to make it look for the active report, but it won't find it because there's no report active, even though I have the report open. How do I make my report be active when I open it? (In a way that whichever report I open will become the active window so that my form will e-mail the report that is currently open)
I want to link into the active directory to get a list of staff usernames, so it will update the user table as new members of staff are created etc. We currently have around 130 staff, so rather than manually updating the user table i want it to pick up the usernames from AD?
a) Open a word document from MS access / VBA, b) Connect to data from a query c) mailmerge it d) Save it as a new document containing mailemerged letters.
The code does open the required document as I can see it open.
I put a message box to recheck that filename/path is what I mean, and to to know till which line the code runs. Code runs till that point and that word doc is seen open. I get the error message before
Then I get error message.
I was fooling around and was getting message " This command is not available because no document is open" I tried to remove few lines thinking redundancy. Before getting the message, I was also getting the error trapping message "No documents opened."
Now I am getting different message.
I am getting message " The remote Server Machine does not Exist or is unavailable"
Following is the code I have written.
Dim xlApp As Object Set xlApp = CreateObject("word.Application")
I have a database that, I would like to add a button that performs a active directory lookup. I would like it to check a username with Active Directory, and auto populate a few fields.
First Name Last Name Manager Department
This is my first database and I have very little exp using VBA.
I am writing the following code to check if 'Active' field in table TypeTable2 is ticked or not for records. If I write the following statement to select the records from Access table where Active is not ticked then it gives "data Type mismatch in criteria expression".
Code: strsql = "SELECT distinct EnvelopeType FROM TypeTable2 where Active='Yes'
I have a main form that controls 2 subforms. sbfActiveProjects displays a list of records that have not been flagged as complete. Upon clicking on a record in sbfActiveProjects, the details of that record show up in sbfProject. This functionality works great, except when I try to create a new record. The code works fine for getting a new record, however once I run the command I lose my ability to navigate to other active records.
Code: Private Sub cmdNewProject_Click() 'set focus to sbfProject sbfProject.SetFocus 'navigate to new record DoCmd.GoToRecord , "", acNewRec End Sub
Essentially I have a form_A with several tab's and then one list control box in each of those tabs. At present if you double click on any item within the control boxes your taken to another form_B with info about that item and when you close that from down again if refreshes form_A.
Is it possible to only refresh the specific list control box that is active instead of refreshing the whole form ?
I'm trying to tie a from into the results from and AD Query. I'm not that experienced with doing this kind of thing so I may be going about this the wrong way. Anyway here's what I've got so far:
Code: Private Sub Form_Open(Cancel As Integer) Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Set cn = New ADODB.Connection With cn .ConnectionString = "Provider=ADSDSOObject;Trusted_Connection=yes;"
[Code] .....
When stepping through the code, it all goes well until it hits the line that actually connects it to the form "Set Me.Recordset = rs".
When this line is executed, Access crashes and attempts to restart.
