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

[Code]...

View Replies


ADVERTISEMENT

Modules & VBA :: Setting Path When Saving Excel Workbook

Nov 1, 2013

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

View 5 Replies View Related

Modules & VBA :: Ask For File Path To Put In A String

Mar 5, 2014

I'm importing a txt file using the following code.

Now my question is, how can I make visual basic ask me to select a file to get the path and put it in a string.

And use this string in the DoCmd.TransfetText

Code:
DoCmd.TransferText acImportDelim, "IN-specification", "tblimport", "C:usersKimdesktop extfile.txt"

View 1 Replies View Related

Modules & VBA :: Fixed Path Information - Function To Return String

Jun 20, 2014

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?

View 10 Replies View Related

Modules & VBA :: Store Path Location Selected By User To A String

Feb 7, 2014

I am using this code

DoCmd.OutputTo acOutputReport, "rptFilter", "Excel97-Excel2003Workbook(*.xls)", strFilename, True, "", , acExportQualityScreen

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.

View 4 Replies View Related

Pass Checkbox Value To A String To Print

Sep 19, 2014

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?

View 2 Replies View Related

File Search To Return Path String

Apr 25, 2007

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

End Sub

View 1 Replies View Related

Modules & VBA :: Save Workbook To Access Db?

May 6, 2014

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?

View 5 Replies View Related

Modules & VBA :: Closing Already Open Workbook

Aug 7, 2014

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)

[Code] ....

View 4 Replies View Related

Modules & VBA :: Copy Worksheet From One Workbook To Another

Sep 24, 2014

I am currently trying to copy a tab from one workbook ("List.xls") to another ("IGEN_QC.xls") but I keep getting an error:

Method 'Copy' of object '_Worksheet' failed

Code:
Sub copydata()
Dim wkbSource As Workbook
Dim wkbDest As Workbook
Dim shttocopy As Worksheet
Dim wbname As String
On Error GoTo ErrHandl

[code]...

View 2 Replies View Related

Modules & VBA :: How To Copy A Query Into Named Range On Workbook

Dec 20, 2013

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

[Code]...

View 2 Replies View Related

Modules & VBA :: Check If Data In A Closed Workbook Is Present In Access Table

Oct 15, 2014

I am using the following line of excel vba code to extract the name of excel file that I would like to compare with Access table data.

Code:
strFile = Application.GetOpenFilename("Excel Files,*.xls*")
textbox1=strfile

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".

View 1 Replies View Related

Modules & VBA :: Open Excel Workbook From Access - Clear Cell Contents

Dec 5, 2013

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.

View 3 Replies View Related

Modules & VBA :: Export Data To Excel Then Link That Workbook Back To MS Access

Apr 17, 2014

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.

View 1 Replies View Related

Modules & VBA :: Separate Numbers And String From Alpha-numeric String

Jun 7, 2013

MS-Access VBA code to separate numbers and string from an alphanumeric string.

Example:

Source: 598790abcdef2T
Output Required: 598790

Source: 5789065432abcdefghijklT
Output Required: 5789065432

View 13 Replies View Related

Modules & VBA :: How To Set The Active Window

Mar 30, 2015

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)

View 2 Replies View Related

Modules & VBA :: How To Get Email From Active Directory

Nov 28, 2013

How to send an email using access but how to get the emails address that are in the active directory...

View 14 Replies View Related

Modules & VBA :: Link Access To Active Directory

May 8, 2014

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?

View 3 Replies View Related

Modules & VBA :: Word Active Document Not Getting Recognized

Dec 26, 2014

I use the following code to achieve following

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")

xlApp.Visible = True
fn = FormPathName("941 2014 AutoOpen.doc")

[Code] .....

View 5 Replies View Related

Modules & VBA :: Access 2010 - Active Directory Lookup

Jul 20, 2015

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.

View 7 Replies View Related

Modules & VBA :: How To Get List Of All Users In Active Directory - Show CN Name

Oct 10, 2013

I would like to get a list of all the users in the active directory and put them in a combo box. How to do this? I would like it to show there CN name

View 9 Replies View Related

Modules & VBA :: Check If Active Field With Yes / No Datatype Is Ticked Or Not

Jan 29, 2015

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'

View 3 Replies View Related

Save Reports To Defined Path In Path Field

May 26, 2014

Till now I managed to publish and save all in C:Reports. What I'm trying to do is Save to path specified in Path. DB attached.

Private Sub PrintAll_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim MyPath As String
Dim MyFileName As String

[Code] ....

View 6 Replies View Related

Modules & VBA :: Creating New Record Within Subform - Cannot Navigate To Other Active Records

May 4, 2015

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

View 2 Replies View Related

Modules & VBA :: Only Refresh Specific List Control Box That Is Active In Form

Jan 28, 2014

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 ?

View 4 Replies View Related

Modules & VBA :: Connect Datasheet Form To Active Directory Search Results

Jul 31, 2013

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.

PS: Access 2010, Win7 64bit

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved