current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0

Tagged Questions

0
votes
1answer
21 views

Automate process by running excel VBA macro in SSIS

Recently, I have a project need to automate a process by combining SSIS package and excel VBA macro into one. Below are the steps: I have a SSIS package exporting all the view result to multiple ...
0
votes
2answers
47 views

Update Excel Sheet Using SQL

I'm attempting to write a procedure that would use an ADODB connection to update records (rows) in a closed workbook. What I can't seem to figure out is how to reference multiple tables (worksheets) ...
-3
votes
0answers
38 views

Excel macro to generate sql create table [on hold]

Hi I need a help on the below scenario. I have a below excel sheet data in a separate columns A B C D E STUDENT S_NAME CHAR 10 1 STUDENT S_LAST CHAR 10 2 STUDENT ...
0
votes
0answers
42 views

Excel Macros: create dynamic sql by reading values from a column in the worksheet

I am creating a macro sheet wherein we'll be firing queries based on the query id selected at runtime. Eg: When a user runs the macro, he will be prompted for entering the query id. {completed} ...
0
votes
0answers
40 views

Excel - Export data

Let's say I have an Excel file (bunch of sheets and macros used in most of those sheets). I have one sheet where I am connecting to SQL Server to pull live data. I am saving the Excel and connection ...
-1
votes
2answers
33 views

Converting lengthy Excel function to SQL?

IFERROR((SUMIFS('Sheet 1'!$K:$K,'Sheet 1'!$A:$A,'Sheet 2'!I$5,'Sheet 1'!$C:$C,'Sheet 2'!$B15,'Sheet 1'!$K:$K,"<>0"))/(SUMIFS('Sheet 1'!$J:$J,'Sheet 1'!$A:$A,'Sheet 2'!I$5,'Sheet 1'!$C:$C,'Sheet ...
0
votes
1answer
42 views

Want to run sql file through VBA

I am trying to connect to the Sybase server through VBA and run the sql file that contains a set of sql lines (sometimes more than 60 lines). I have written the code as below. However, it is giving ...
0
votes
0answers
14 views

ORACLE ORA00907: Differences in connection string using OraOLEDB.Oracle vs TNS lookup via Oracle in OraClient10g_home3

I am using Oracle Client 10.2g and by changing my connection string to the Oracle database I now get error ORA00907 for some of my queries. The code is executing within excel 2010 using VBA and I can ...
0
votes
0answers
10 views

Not creating table when inserting data into access table through excel 2013

Although I do not get any error messages, my code is not creating the table in my access file. The code used to execute properly and suddenly it stopped. If I execute manually the query to create the ...
0
votes
0answers
23 views

How to add Query results to Combobox

Here i am trying add to add results of query into combobox but am able to add to combobox wherever the query result restricted to one value but more than not able to add it. Please help me on this ...
0
votes
0answers
26 views

SQL Query in VBA without Import into Excel

Ok, got an easier one for you guys as my last few questions have seemed to stump people. Currently, I do the following to execute a SQL query with VBA in Excel. sqlStr = "SELECT * FROM ITEM" ...
0
votes
0answers
21 views

I have a specific fields from an Access 2007 table that I need to put in specific cells in an existing excel sheet

I have spent days doing research on how to accomplish getting specific fields from an Access 2007 table to an existing Excel sheet in specific cells. The excel sheet is highly dependent on the Access ...
1
vote
1answer
41 views

How do I pull a certain table from Internet Explorer in Excel VBA

Hello I've scoured the internet trying to find a solution to this problem. I'm trying to pull a certain table from this a private website using excel-vba. I came across this code that allows me to ...
0
votes
0answers
32 views

How to avoid Excel not responding when SQL server rejects the connection - Excel VBA

I wrote my Excel VBA application to work with my company SQL server, randomly, the SQL server denies my access (weird but IT does not bother to figure out why). When it happens, my Excel file will not ...
-2
votes
1answer
42 views

combine two sql queries for excel vba

I wrote a code in excel VBA which was supposed to extract data from the SQL server. There is "tbl.id" has a relation in two tables. In my third columnI get the "cash-drop" and on the forth column I ...
0
votes
0answers
22 views

authentication probem excel 12 to sql Server

i have a problem with a excel macro, when i try to open a connection to SQL server from office 2007 this dont work, but in office 2003 works fine, can any one say why? Public Function ...
-1
votes
1answer
20 views

Apply Count formula in a Excel Row with conditions True/False

I have to apply a logical count formula on a column which contains some value. It contains identical and non-identical numbers. What I want is a way to apply a formula to the next column which will ...
-6
votes
0answers
45 views

Comparing two column placed in two sheets and found mismatched values and paste

I have two sheets. FINAL DATA and STORE MANGER DATA. Sheet FINAL DATA, column B is having some text file ranging from B2 to B654. Same way Sheet STORE MANGER DATA , column C is having some text ...
-4
votes
1answer
40 views

excel : macros for copy paste from one sheet to another sheet column [closed]

I have little knowledge in macros. But I can't able to find out answer to me. I have an excel workbook with two sheets naming MMS DATA anad RHQ DATA.Sheet RHQ DATA have some texts in column D ranging ...
0
votes
1answer
56 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 ...
0
votes
0answers
38 views

Reading excel 2007 workbook with more than 255 columns

I writing a macro that reads in two keys and returns back a value using those keys. The data that I am returning values from is formatted as a matrix where one key is a row and the other is a column. ...
-1
votes
0answers
16 views

fetching data from SQL server using Input from excel using macro

Hi I am trying to fetch a few columns in a table using the input I have in my excel sheet. How can I do it? Sample data: input in excel : 1 2 3 4 Table in sql: table1 c1 c2 c3 c4 c5 c6 a0 b0 ...
2
votes
1answer
39 views

SQL Join with Order by and Group by

Hi Im trying to do a Join in Excel from another Excelworkbook. The primary key in the first table is Part_No. This is not the primary key so a 1 to 1 join can't be made. I want to choose the best ...
0
votes
0answers
33 views

Pushing data back to SQL Server

A coworker has a REALLY complex Excel workbook that consists of several pivot tables (which all pull their data from a SQL Server DB), and a worksheet that is a prettier view of all of that data ...
0
votes
2answers
61 views

Copy unique records from one workbook to another master workbook

I need some help with copying unique records from one workbook to a master workbook please. Each month I receive a new workbook with data and I want to be able to copy all new records in that new ...
0
votes
0answers
31 views

Generate column numbers using dynamic SQL and pivot command

I have the following SQL which i want to convert to a stored procedure having dynamic SQL to generate column numbers (1 to 52) for Sale_Week. Also, I want to call this stored procedure from Excel ...
0
votes
2answers
34 views

Insert Rows From Excel into SQL Server

I am working on an insert from Excel 2010 into SQL Server 2008. I have been able to successfully insert a single cell value into a SQL table from excel using a push button. Now I am trying to insert ...
0
votes
1answer
21 views

Excel 2010 to Sql Server 2008 Insert Statment

I am going from Excel to Sql. I have the connection established. I can create a simple select statment and obtain values from a table in Sql into Excel. Now, I want to go the other way. I am trying to ...
1
vote
2answers
39 views

Stored Procedure vs Direct Query in Excel

I have an excel file that will select roughly 1100 rows with 5 columns of data. Most columns are 5 digits long and are integers. I am using a macro to connect to a SQL server database and insert ...
0
votes
0answers
39 views

Excel Ado sql concatenate queries

Now i count if "BR" is in cell with query 1 sql ActiveWorkbook.FullName, "SELECT " & _ " trim(`F1`), " & _ " SUM(IIF( TRIM(`F1`) IS NULL ,1,1)), " & _ " ...
0
votes
0answers
36 views

Excel VBA , Creating Querytable using VBA

I have the following code in VBA, the problem is that if I email it to someone, it stops working. So I wanted to hard code the Add QueryTable feature. My question is, where do I add the Querytable.Add ...
0
votes
0answers
20 views

Excel: OLEDB Connection to Save to different workbook

I am trying to create a macro that will allow me to retrieve and save data from another workbook. I am able to retrieve the information without any issues but I am unable to save it back. The Data ...
1
vote
0answers
49 views

Excel VBA ODBC error on SQL Server

I need some suggestion about excel and VBA. My system is: MS SQL Server 2008R2 Ms Excel 2003 ODBC set and working I've a vba code to retrieve data from a SQL server. The code is: Sub ...
0
votes
0answers
21 views

vba excel get sql column names

I have the following vba code which imports data from SQL DB, but I can't get column names could you help me with that please. Here's my code: Sub a() Dim connection As ADODB.connection Dim recordset ...
0
votes
0answers
25 views

excel vba recordset.open fails for 2-character column addresses

I have been using ADO to import values from a closed Excel workbook. All is well until the SQL query 'source address' string contains a 2-character address like "AA3:AB40". Any ideas for a ...
0
votes
0answers
69 views

Excel VBA ADO query to microsoft access returning more records than expected

My excel/VBA ADO query is returning more records than i expect. When i run the query manually in ms access, it shows 59 records which is correct. When I run the import from excel using some VBA, it ...
0
votes
2answers
21 views

Determining if Spreadsheet Entries Match Database Column Entries

One aspect of my project involves comparing the part number entered by the operator to a predetermined list of part numbers in a column in a database. Right now, my program is telling me that every ...
0
votes
1answer
26 views

Type Mismatch error in Initialization form code calling an Access data table

I am now encountering an issue in my project that seems to have occurred without any code alteration. As a background, my project got corrupted so I consequently redid the entire project in another ...
0
votes
0answers
19 views

Convert contents into header - Excel or Access

My raw data is stored in Excel and Access. There are thousand of rows. Team Job Title Application Access Level T1 BA App1 Read-only T1 BA App1 Edit T1 BA App1 Delete T2 Line Mgr ...
0
votes
1answer
48 views

Excel-to-SQL stored procedure with multiple parameters

I have a stored procedure with four parameters, I'm trying to connect it to an Excel 2010 workbook but I'm having difficulty passing multiple parameters. I followed the steps outlined in Running a ...
0
votes
0answers
49 views

How to use Two arrays in VBA to pull information from an SQL table and insert it into various columns in excel

I am trying to create two arrays that can be used in the same For Loop col_School = Array(2, 3, 4, 5, 6, 7, 8, 9) schoolArray = Array("051", "052", "061", "062", "063", "071", "072", "073") ...
0
votes
0answers
24 views

How to copy the null values from a SQL query to a spreadsheet using QueryTable method in VBA

I was hoping if someone can please help me to find a way to copy the null values from a SQL query to a spreadsheet using QueryTable method in VBA? Currently I am getting blank cells if I use the below ...
0
votes
1answer
112 views

VBA Excel SQL Server INSERT query

I'm having trouble with an SQL Server INSERT Query. First, here's what I am trying to achieve: I have a list of product in an excel worksheet that I would like to export into a MS SQL Server. Here ...
0
votes
0answers
366 views

Excel VBA and ADO - Error 3704 Operation is not allowed when object is closed

Im attempting to add data stored on an excel spreadsheet to an access database. But, before each row is added, i want to check if that row of data is already present in the database, and if so, skip ...
0
votes
1answer
24 views

ADO Count Type Mismatch

I am referring from one of l my previous post Syntax of SELECT COUNT in ADO I have an ADO connection between my Excel file and another Excel file that works correctly Set adoConn = ...
0
votes
2answers
102 views

VBA passing date in stored procedure problems

I'm having difficulty passing a Date value to a datetime value using a SQL Stored Procedure. The @DateTime parameter is in datetime format in MyDb in a Microsoft SQL Server. When I run the below ...
1
vote
0answers
57 views

Can't run distinct ADODB query on open workbook?

I have a class ADOConnector for creating an ADODB connection to a workbook and querying the workbook: Private objconnection As New ADODB.Connection Sub connect(workbookPath As String) On Error ...
1
vote
1answer
580 views

How to select distinct values from one column in adodb recordset Excel VBA?

I have a ADODB.Recordset rs that I'm getting from DB. I have to reuse this recordset twice now. This is sample of my Recordset: Mike Client John Manager Karen Client Joe Sub Brian ...
0
votes
0answers
46 views

can we create index in excel as in sql for quick search

I have jumbled attribute values in columns C, D, E, F of sheet1. I have arranged data in sheet2. I need to compare sheet1 and sheet2 if any row in sheet1 matches with sheet2 then need to inset ...
0
votes
1answer
63 views

Import multiple Exel files/sheets into SQL Table

I have about 100 xlsx files, all with 1-7 sheets each. Each file and sheet has the same columns as the table I want to import everything into. I can use this successfully: SELECT * FROM OPENROWSET( ...

1 2 3 4 5 6