Tagged Questions
1
vote
0answers
20 views
VBA Excel - UPDATE record in ACCESS
I got a problem.
I want to update an existing record in table in Access Database using VBA in EXCEL.
My code:
sqlik = "UPDATE query which works in access"
Set ZAP_QUERY2 = ...
0
votes
0answers
43 views
Unable to connect to Access database with ADO
I need to import a bunch of tables stored in .txt files into an Access database. When they are done importing, I use an ADO connection to communicate between the database and an Excel workbook. I have ...
0
votes
1answer
253 views
MS Access VBA linking data and updating records from excel
What is the best approach to updating records that are saved in an excel file. For instance I have a MS Access database with a table in it that has Product ID, ProductName, and ProductAge. I have this ...
1
vote
1answer
155 views
Importing data from Excel into Access using DAO and WHERE clause
I need to import certain information from an Excel file into an Access DB and in order to do this, I am using DAO.
The user gets the excel source file from a system, he does not need to directly ...
-1
votes
1answer
637 views
how to read data from an excel worksheet dynamically using VBA 2003
I am new to vba programming hence I need your expert help in trying to be able to able to read all values from the following excel sheet into a ADODB recordset object using VBA 2003
The recordset ...
0
votes
1answer
58 views
Does the recordset close itself when called from within a function?
In VBA Programming I am trying to understand the state of a recordset when it is passes data from within a function to another recordset
for example
Sub Test()
Dim Recordset1 as new ABODB.Recordset
...
4
votes
1answer
186 views
Create a DLL for VBA Class Module or better alternative?
Here's my issue: I wrote a VBA class module that consists of commonly used methods and functions. I am continually refining the module and making changes to it.
The problem is that I include this ...
0
votes
1answer
234 views
Sort/Manipulate Excel data from Access 2003 VBA
I'm trying to run a module in Access 2003 that takes an excel file, if there is info in the cell, then copy the field header and cell in A1, B1, etc... as it loops through each row. There are also ...
0
votes
0answers
97 views
How to read (query), format, and export a sheet to a existing XLS, in a Access/VBA application?
I have an introductory (?) question about Access/VBA to xls exporting.
Here is my scenario:
We have an Access database and a form (created in the Access environment) with some buttons where the user ...
1
vote
1answer
175 views
System Information via VBA code
I was wondering if there is any way to retrieve System Information via VBA code?
Is there such a class as - SystemInfo Class in Excel VBA to do this task?
thanks
1
vote
1answer
651 views
MS Office VBA changes from 2003 to 2010
in my company the IT-department finally decided to switch from MS Office 2003 to 2010. In my department we developped many small Applications running on VBA. Particularly all of them were programmed ...
0
votes
1answer
101 views
Getting different results while running the same stored Access SQL query directly and through VBA excel
Hey the following stored access query is giving POEventCode with "NA" when it is run through VBA excel and is giving the correct result when directly run from access.Why is this anomaly?
SELECT ...
3
votes
1answer
985 views
mySQL-query from Microsoft Access VBA fails if not interactive?
I'm fighting with a VBA non-interactive problem that really bugs me: I have a pass-through query to a mysql database, which works well if double-clicked by the user. But it fails if called from VBA ...
1
vote
1answer
383 views
VBA to write data into excel used by multiple users on a network?
Basically i have a master workbook on a network drive which is used by many to update the sheet,i want to automate this , through my research i found there are many complications with it if i automate ...
0
votes
1answer
132 views
slow exporting array from access to excel
I have a similar performance issue to that in slow exporting from access to excel, exporting data to MS Excel by setting individual cell values looping on:
objSheet.Cells(table_1_row_no + intcounter, ...
0
votes
2answers
780 views
Import data from Excel into MS Access [closed]
I have an excel file with some data, I need to import all the data into MS Access using VBA, and then run a query on it, and finally present the data in a report.
How can this be acheived?
0
votes
1answer
135 views
Get value of variable used in Access report
I have a report ("rptEstimate") used as part of a larger program that runs in Access. This report has a text field, with the expression =Nz([txtTaxTotal],0)+Nz([txtSubTotal],0). Where can I find the ...
1
vote
0answers
423 views
Adding a format condition in Excel through Access VBA
So currently i got these lines in my code,
.Range("C2").Formula = "=IF(B2 = """"," & """REQUIRED""" & "," & """""" & ") "
.Range("C3").Formula = "=IF(B3 = """"," & """REQUIRED""" ...
0
votes
0answers
163 views
how can I control an Excel OLE object from access with vba
I would like to have an access vba procedure to write in an embeded Excel sheet.
I've added an Excel Ole Object to my Access form, but I'm unable to address it.
How shoud I do that ?
11
votes
2answers
4k views
How to comment and uncomment blocks of code in the Office VBA Editor
In the VBA editor of Office (ALT + F11), how do you comment or uncomment a block of code?
0
votes
1answer
162 views
Run sub procedure in excel after values passed from MS Access record
I have an Access database which successfully passes the values in my current record to an Excel sheet. To calculate my Excel sheet I then need to call a subroutine.
I think my problem is the order ...
1
vote
2answers
2k views
Excel VBA Connection to Access 2010
I have a class that's handling my connection to an Access 2003 database. I would like to setup the same thing only for Access 07/10 .accdb files. Any help is appreciated! Thank you!
Here's a list of ...
1
vote
1answer
717 views
Opening an Excel workbook macro from Access
First of all, I have searched and read similar threads and I have tried the solutions that were proposed and solved the other people's problem (mostly using single quotes around the workbook name) but ...
0
votes
0answers
251 views
Link cells in Excel to Access table fields
Consider I have a macro in access 2003 that uses the transferSpreadsheet function to export a Union query to a excel 2007 spreadsheet; the exported data depending on a date range input by the user.
...
1
vote
1answer
119 views
Can I use a string as conditional compiling Constant
Is it possible to use strings constants in conditional compilling in VBA?
for example:
#Const This_File_Concept="Chancleta"
'
#If This_File_Concept="Chancleta" then
''...Something happens
#End ...
0
votes
2answers
939 views
MS Access VBA: Reference Excel Application Object created in separate Module
This seems like it should be an easy one but I'm stuck.
I'm running a VBA script in Access that creates a 40+ page report in Excel.
I am creating an Excel Application Object using Early Binding:
...
0
votes
1answer
946 views
Get table data in Excel 2007 from query in Access 2007
I have an automated process that is mostly run in Access. But, in the middle, it puts some data in Excel to scrub it into the correct form (it's much faster than doing it in Access), and at the end ...
0
votes
0answers
84 views
ADOX.Catalog.GetObjectOwner => VBA vs. DB2
I'm using VBA to query a DB2 database. I would like to get the Object owner of a couple of tables using the ADOX Catalog object. This doesn't seem to be supported? Would anyone be able to confirm that ...
9
votes
3answers
8k views
Why use arrays in VBA when there are collections?
many people use extensively arrays in Excel/VBA to store a list of data. However, there is the collection object which in my view is MUCH MUCH more convenient (mainly: don't need to re/define length ...
10
votes
2answers
23k views
Loop through files in a folder using VBA?
I would like to loop through the files of a directory using VBA (Excel 2010). In the loop, I will need the filename and the date at which the file was formatted.
I have coded the following which ...
2
votes
1answer
1k views
Access query to excel 2010 to create a graph via vba
After an extensive search I have been unable to find any information on this that I could understand. there are numerous examples, but these are all for access 2003, and these do not work in access ...
7
votes
1answer
815 views
How to monitor the values in a Dictionary in the Excel VBA watch window?
I am using dictionaries in Excel VBA via dict As New Dictionary (and adding a reference to the scripting runtime). When I try to monitor those during debugging, I can only see the keys which lie in ...
0
votes
1answer
1k views
How to hide columns in a ComboBox dropdown?
I'm building a ComboBox in an Excel userform that gets its rows from an Access table. I want to display several text fields to the user in the dropdown, but the value returned from the ComboBox ...
1
vote
1answer
2k views
Changing a tabledef .connect property in VBA
I am trying to do something that I have done a hundred times, but it suddenly won't work.
acc.AutomationSecurity = msoAutomationSecurityLow 'Remove Security Prompt
acc.OpenCurrentDatabase path ...
0
votes
0answers
886 views
Automate sending emails in VBA via Outlook Web App
Is there a way to send automated emails in Excel/Access VBA using Outlook Web App?
0
votes
1answer
339 views
Comparing fields between excel and access and fill
What i wanted to know is how do i get a vb application reading an excel file and a access database at same time, make them compare the fields and fill fields that are null.
for example:
Excel has ...
1
vote
1answer
167 views
How to refer to cell without A1 notation?
In my Access VBA I have hyperlink, which is using the following way to link to cell:
oSheet.Cells(1, i).Formula = "=HYPERLINK(""#Sheet2!E6"", """ & !TestCase & """)"
However, instead of E6, ...
0
votes
1answer
772 views
Copying data from a MS Access form into Excel
I have code that takes fields from a MS Access form and copies the data into a saved Excel file. The first record in Access in imported to Excel with a range of A2:I2. The second record in Access is ...
0
votes
1answer
526 views
VBA two dimensional arrays connecting to a database
I am working with vba in excel and a database in access. The access database is a table that contains 3 columns; OrderIDs which is a column of numbers saying what order the particular item was in, ...
4
votes
2answers
1k views
Excel-Access ADO Update Values
I am trying to update a table in Access from the values in excel, however every time i run the code it creates new rows instead of updating the already existing ones, any ideas why? I am new to ADO, ...
4
votes
1answer
2k views
Excel - VBA - Save Data to Access Database
I'm working on excel with VBA and access and i need my system to save its data to an Access database and whenever any new records added, deleted or modified the database should update.
I would like ...
1
vote
2answers
105 views
Can I fire-up an Access 2007 app from within Excel and automatically import excel values into Access?
I have a pawnshop app where when a customer arrives at the shop, the clerk uses an Excel worksheet to calculate how much money he can lend to the customer, based on the current market price of gold, ...
0
votes
3answers
104 views
Transform Excel files to the existing database via the form in Access
I has not a lot of experience in VBA programming. What I need - is the possibility to create a simple form to extract some Excel file to the defined table by adding the records.
Could you please tell ...
0
votes
2answers
1k views
oAuth client in Office VBA what is difficult?
I cannot find any oAuth sample code and saw others were desperately searching for it, what's the difficult part to implement an oAuth client in VBA ?
0
votes
1answer
1k views
MS Access Auto Link Excel Spreadsheets
I have a directory structure where I am managing the requirements of a system with each component of that system having its own directory. the requirements of each component are stored in a excel ...
1
vote
1answer
646 views
Updating a table Access and Excel VBA
I have one table called: Transaction. This table has the following fields: (ID,ProductName,Amount,Date) placed in an excel sheet that is connected with MS Access database. ID is the only unique field. ...
6
votes
3answers
8k views
Connection string syntax for Classic ADO / ODBC / Oracle 10g EZConnect
I'm trying to connect various VBA projects to an Oracle 10g back end using ADO (2.8) and no TNS. After various attempts, we've decided that the simplest series of steps for a clean installation ...
0
votes
2answers
6k views
Open an Access database and run one of its Macros from Excel
From Excel, I need to open an Access database and run one of the database's macros.
I'm using Excel and Access 2007. Here is my code in Excel:
Sub accessMacro()
Dim appAccess As New ...
2
votes
1answer
1k views
How to reference an embedded PowerPoint or Excel file in a form in Access?
So if I was to take a an Access form, and embed either an Excel spreadsheet into it or a PowerPoint deck, how would I reference it in VBA code?
I know I have to set the libraries, name the frame of ...
2
votes
1answer
422 views
Excel 2003 VBA - Method to duplicate this code that select and colors rows
so this is a fragment of a procedure that exports a dataset from access to excel
Dim rs As Recordset
Dim intMaxCol As Integer
Dim intMaxRow As Integer
Dim objxls As Excel.Application
Dim objWkb As ...
