current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0

Tagged Questions

1
vote
1answer
27 views

Old workbook (once opened by VBA) keeps opening when I open another excel file

I am building a database in Access, for which I import data from an Excel workbook questionnaire. I have coded an Import-sub that selects, opens, retrieves the data from and finally closes the ...
-2
votes
0answers
12 views

Import flow from Access to Excel to Outlook Calendar

I know zero about VBA but know that the following can be done. I have seen the rear-end of one portion of this issue here: Importing data from Access into Excel But I need to know how to get to that ...
0
votes
0answers
28 views

Access:Type Conversion Failure when insert table from linktable

I got below source code from website, the source code is for copying excel file into a table of access The linktable is created and data are all imported ,no any issue but when run Docmd.runsql ...
-1
votes
1answer
52 views

ORDER COLUMNS TABLE VBA

I would like to reorder all the columns from several tables (Set as Table in Excel) one below the other adressed in the same Worksheet. This is an example so you can have a better understanding of ...
0
votes
1answer
29 views

Importing dozens of xls files into Access

I have an excel spreadsheet that has 5 different tabs. I would like to create this spreadsheet in access represented by 5 different tables. My first attempt at this is to import in the files. I have ...
1
vote
1answer
35 views

xlLastCell only works half of the time

I am trying to remove the duplicates in excel via the following code: Set rng = Range("A1", Range("A1").SpecialCells(xlLastCell)) rng.removeduplicates Columns:=8, Header:=xlYes This method ...
1
vote
1answer
37 views

Only user -defined type defined in pubic object modules can be coerced when trying to call an external VBA function

I am trying to call an Access function from Excel and get this error: "Compile Error: Only user-defined types defined in public object modules can be coerced to or from a variant or passed to ...
1
vote
1answer
32 views

Is it possible to search for, then to read in specific values from an Excel file using Access VBA?

I have had to read in values from an excel file before, but it was a Excel comma separated file. This time I have an excel file with a graph and two tables. I only want to read in the values in one ...
2
votes
1answer
87 views

VBA Automation Error (System call failed) while running Excel 2013 macro from Access 2013

I have an Access (2013) database that I use to store/process all of our data received from a Qualtrics online survey. The raw data downloaded from Qualtrics is in a csv file that's poorly formatted ...
0
votes
2answers
51 views

Keep Excel sub runing after calling access sub procedure

within my Excel sub I am calling an Access sub procedure that runs in the background (DB does not open) and updates a table in my DB. Everything works perfect, except that my Excel Sub will not ...
0
votes
1answer
33 views

Pass variable value to batch file

Can't pass the variable value to a batch file: VBA code executed when clicked on an Access 2007 form button. Private Sub OpenWB(var1 As String) Dim RetVal RetVal = ...
0
votes
1answer
59 views

How to insert a field from Excel Sheet to Access Database with a WHERE condition

I've been wracking my mind over this problem for a while and I was wondering if there is a solution! So I have a table called R1 with two Column: [Department] and [DepartmentQV] in Access, the ...
-3
votes
2answers
100 views

How to go through free text and store values?

I have a large excel workbook with several columns. I want to go through one column (that contains free text) and store every word in a new sheet. (Not unique.) Sample data (source sheet): ...
0
votes
1answer
66 views

Can Any one help me how to bind a code which pulls up a button automatically and a code which sends mass email

This is the code which pulls up a button: Sub a() Dim btn As Button Application.ScreenUpdating = False ActiveSheet.Buttons.Delete Dim t As Range For i = 1 To 3 Set ...
1
vote
1answer
53 views

Do we need to declared the variable “x” in a “FOR EACH x IN y” loop?

Im working with VBA in MS ACCESS 2013. One of my Modules has a For Each fld In rs.Fields and does not require me to declare the variable fld. On my new function i have the same For Each fld In ...
0
votes
2answers
32 views

Deleting rows in Excel based on criteria via Access VBA

I am working on a module that will format an Excel spreadsheet for import into Access. The spreadsheet comes from the source with 7 rows of header data, the data I need, and 4 rows of jibberish below ...
0
votes
3answers
58 views

VBA Declaring arguments outside a function

I have vba code which opens a word document based on a template and when finished, runs the code below: Public Sub Destroy(doc As Word.Document, app As Word.Application) If Not (doc Is Nothing) ...
0
votes
0answers
32 views

Access 2010 Linked Tables pointing to Excel workbooks and TypeGuessRows

I have an Excel file that has a mix of text and numbers in a worksheet. In some cases, the first 8-10 rows have numbers, and text occurs in rows after this. The default value for TypeGuessRows is 8, ...
1
vote
1answer
72 views

Stop Access 2007 From Using Scientific Notation When Importing Data

So I am importing a giant list of part numbers from an Excel file (converted from a CSV report from somewhere else) with VBA. The excel workbook is called on to prepare the data before importing ...
0
votes
2answers
69 views

Return Drive name to VBA in Access from batch file

I have a Batch file that maps the next available free drive letter to a network drive. I use this to map a sharepoint site and then use the documents in the shared folder in my Access database. this ...
0
votes
0answers
47 views

Access VBA Error 1004 Cannot Paste Data in Excel

I have the following code to copy the content of an Excel file to another file. Set objExcel = CreateObject("Excel.Application") Set objWorkbook = objExcel.Workbooks.Open(strTempPath & f.Name) ...
0
votes
0answers
33 views

VBA loop though visible column in printing

I have a pivot table and I sliced my printing table to filter selection. SO when filter is ALL it show all of my list; However I would like to print my pages and print filter selections as the title ...
0
votes
0answers
52 views

Microsoft Access VBA to Interact with Excel Generating Large Phantom Files

I am writing a VBA/SQL script to pull selective information from 3 large Excel files into a centralized Access database. I noticed, however, that my free space on C shrinks about 100 MB each time I ...
2
votes
2answers
75 views

Does VBA contain a comment block syntax?

In VBA is there a short way to comment out a block of code the same way java uses /*...*/?
2
votes
1answer
65 views

Is it possible to access a parent property from a child that is in a collection?

I've researched as much as I can and never found a definitive answer on this for VBA. This older StackOverflow post has almost everything, but not quite. Excel VBA Classes - How to have a class ...
0
votes
1answer
64 views

VBA Macro to subtract dates by duplicates ID

I would like to create a Macro in VBA to identify the first date and the last date by ID and then get the subtract result between them formatted in hours. This is an example of my Table: ID ...
0
votes
1answer
46 views

how to split a string with multiple delimeters in vba?

I am trying to split a string. I am using the split function, but I am unable to do for multiple delimiters. Course Details = Split("ENM/5200-Fall2014/aba","/")
1
vote
1answer
53 views

Late Binding UserForm Functions

I have 2 userforms: Userform1 Userform2 They both have the function UpdateFields, which is implemented differently in each. I have 3rd function that takes as input a user form and tries to call the ...
-1
votes
1answer
69 views

Is it possible to append Excel data to an Access database file using VBA within Excel?

I have three cells in an in Excel 2010 worksheet, say a1, a2, and a3. Every time the user runs my Excel macro, I need it to take the info in those cells and append it to an existing Access DB file. ...
1
vote
1answer
72 views

How do I reference a specific external app window (object) in VBA?

Im currently creating an app in Microsoft Access that references the "Reflections for regis graphics" library. Bascially the below code will create a new instance of reflections which is an external ...
-1
votes
2answers
144 views

how to get excel column number from column name using access vba

I'm trying to get the excel column number (index) from the column name. Dim ExcelApp As New Excel.Application Dim ExcelBook As New Excel.Workbook Dim ColName As string ColName = "sample name" ...
0
votes
1answer
71 views

Save attachment to hard drive

I am trying to code a simple task: retrieving an attachment from Access (2013) database and saving it to disk. At the moment I would like the code to get a first record from recordset and save the ...
0
votes
0answers
134 views

How to disable the yellow Enable Content bar, MS Access 2010

When i'm trying to open access 2010 form from excel, while access is opening it is showing yellow enable content bar, and in my access form i can't see list box items and button click is not working. ...
0
votes
1answer
97 views

Get Current user's Workstation name logged into Host computer

I have a host machine running windows-7 and users from the home network login using windows remote desktop connection and run some applications , I can visually see the log of who logged in under ...
0
votes
0answers
91 views

Getting excel file path from access vba

I'm trying to get excel file path from access vba while desired excel work book is closed. I've written function in excel and calling from access Excel Method Public Sub writexlpath(flpath As ...
0
votes
0answers
35 views

Overflow error for #NUM! field in Access - Excel vba

I'm getting an Overflow error in Excel when I try to run one of my macros that's supposed to get some data from Access. The Macro works fine but when I try to get some values from a querry where I ...
0
votes
1answer
23 views

Delete table in access with String with 3 values

Good day, Im getting an error (syntax error missing operator in a query expression for "and monitorNumber = Monit) SQLDel = "DELETE FROM HBAU WHERE Peoplesoft ID ='" & PSID & "' and DATE =#" ...
0
votes
0answers
364 views

How to calculate monthly salary using an Access Database Attendance Log?

I have made an Access 2007 database for Attendance Log for a company. The database contains the following tables, forms and queries, Employee Details Form and Table which contains the field - ...
0
votes
1answer
142 views

How to set IMEX=1 in Excel

I am using OS Windows 7 and Office 2010 and SQL Server 2008/2008R2/2012. I am trying to import excel sheet to SQL Server. One column has several Numeric and Text values as well. While importing data ...
1
vote
1answer
513 views

Call excel function in access vba

I've a sub in excel that needs to be called from access. Excel vba Public Function testme(value As String) As String Dim xlpath As String Dim concate As String xlpath=ActiveWorkbook.Path ...
0
votes
2answers
235 views

How to pass a string contains excel file path in excel vba to access vba

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. ...
2
votes
2answers
320 views

Remove Excel task from Task manager after running from Access using VBA

I will now take the opportunity to ask here, I have really tried a lot of different way, but it seems that I am not able to be able to close the Excel task in task-manger, It hangs until I close ...
0
votes
2answers
59 views

Check whether workbook, being accessed within MS Access via VBA, is open

I am opening a workbook from Access via VBA using the following code. I need to check whether that workbook is already open: if already open then do nothing, otherwise open it with CreateObject ...
0
votes
3answers
42 views

How can I set up an Access VBA library

I have some VBA code that I use across multiple projects - about 40 or so different data bases all use many of the same functions. I try to use the same code in application each for consistency. Is ...
0
votes
1answer
129 views

Determining a DAO Access Table Field's Format to apply to an Excel Worksheet

I am using the CopyFromRecordset command within Access to copy a series of tables into Excel. No worries with this process. My problem is making this routine be able to determine what format should be ...
0
votes
1answer
491 views

Delete all records from table - doCMD.RunSQL

I am looking to clear a local table of all records before adding new data to it. I am trying to do this using the doCMD.RunSQL command but keep receiving run time error I am guessing because of its ...
0
votes
1answer
263 views

Autofilter Excel with VBA

I would like to open Excel from Access and apply filters to a sheet. Below is my code: Dim s as String Set oApp = CreateObject("Excel.Application") oApp.Wworkbooks.Open FileName:="dudel.xlsm" ...
4
votes
2answers
204 views

How to check for record by using ID, then if record exists update if not add new record

I've create an excel userform to collect data. I have connected it to Access to dump the data. However I want to update Access every time a user presses the submit button. Basically I need the ...
-2
votes
1answer
167 views

How to transfer more than 65536 rows from excel to Access - Version 2010 [duplicate]

I have the following code to transfer some rows from Excel to an Access Data Base, then it export from Access to a .txt file. The problem is that when I export it to Access, it only exports 65536 ...
0
votes
2answers
532 views

Method 'Cells' of Object'_Global' failed VBA

EDIT: i have an access application in which i need to open an existing excel sheet, find a date (already in the sheet) and populate a row (with the date cell column) with either 1 or 0 this means i ...

1 2 3