Go Premium for a chance to win a PS4. Enter to Win

x

Microsoft Excel

132K

Solutions

37K

Contributors

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Share tech news, updates, or what's on your mind.

Sign up to Post

I am trying to extract a long string value from a closed worksheet and ExecuteExcel4Macro returns Error 2015 - the string  is longer than 255 chars.

I  have switched to ADO and am having problems with the connection string, it generates an error

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\john\Documents\BSMA\Financials\OpenInvoices\2017-03\2017-03-29 243 Carnival UK.xls;Extended Properties="Excel 12.0;HDR=No;";

The Sub Routine Code itself is based on a sample I found on the Web

Public Sub GetCrewData(SourceFile As Variant, SourceSheet As String, _
                   SourceRange As String, TargetRange As Range, Header As Boolean, UseHeaderRow As Boolean)

    Dim rsCon As Object
    Dim rsData As Object
    Dim szConnect As String
    Dim szSQL As String
    Dim lCount As Long

    ' Create the connection string.
    If Header = False Then
        If Val(Application.Version) < 12 Then
            szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=" & SourceFile & ";" & _
                        "Extended Properties=""Excel 8.0;HDR=No"";"
        Else
            szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                        "Data Source=" & SourceFile & ";" & _
                        "Extended Properties=""Excel 12.0;HDR=No;"";"
        End If
    Else
        If Val(Application.Version) < 12 Then
            szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data 

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE
LVL 5
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Hi everyone!
I've encountered a problem of combining different databases together in a single query. I'm searching for the way to get a desired result, but no success so far..". Possibly someone could point me to a right solution.

Description:
  1. There are 2 databases (DB): DB1 - Visual FoxPro database and DB2 - MS Access database
  2. Quering databases is organized with ADO
  3. In DB1 I have all the necessary information except a list of sold items. Sold Items list is located in DB2
  4. I need to query DB1 and list all the models, which have not been sold yet
  5. All the queries must be performed from inside Excel and returned to excel as well

A sample of simplified SQL query:
[VEHICLES] - Table from DB1 (Visual FoxPro)
[SOLD] - Table from DB2 (MS Access)
"	SELECT [VEHICLES].VEH_MODELS FROM [VEHICLES] " & _
"	LEFT JOIN [SOLD].VEH_MODELS FROM [SOLD] " & _
"		ON [SOLD].CODE=[VEHICLES].CODE " & _
"	WHERE [SOLD].VEH_MODELS IS NULL"

Open in new window

Problem: How can I make ADO understand which table is in each DB?

Initially ADODB Connection.string is set to use Visual FoxPro driver and DB1 location
(Example: ADOcon.ConnectionString="Provider=vfpoledb;Data Source=" & DB_Path & ";Collating Sequence=machine;")

Possibly anyone has faced such problems previously and could suggest smth?
P.S. Please don't ask why data is in separate databases =))
0
Hi,

I have an Excel workbook that contains data in multiple tags.

I am trying to export to a PDF file but I know see the first page when I opened the PDF file.

Please advise how I can do this.

Thanks.
0
excel vba
I have data i'm copying to another workbook, from my current workbook.

Dim curWks As Worksheet
Dim templWks As Worksheet
Dim rngToCopy As Range

  
Set curWks = ActiveSheet
With curWks

Set rngToCopy = .Range("A1:AX65453", .Range("a1").End(xlToRight).End(xlDown))
End With

Workbooks.Open _
fileName:="C:\Program Files\enterprise\Customer Copy\Customer_Template.xlsx"


If ActiveSheet.Name = "Project Data" Then
Else
Sheets("project Data").Activate
End If

Set templWks = ActiveSheet
templWks.Cells.Select
templWks.Cells.Clear

' APPENDS DATA
rngToCopy.Copy _
Destination:=templWks.Range("A65453").End(xlUp)


' after i copy data i need to insert a column at  Column P


Workbooks("Customer_Template.xlsx").Close SaveChanges:=True
Application.CutCopyMode = False

Open in new window



What I need:
after i copy data i need to insert a column at  Column P in the other workbook.
And give the Column Header a name "Customer  Price"


Thanks
fordraiders
0
OK...without going into all of the details of exactly what I'm doing:

I have a combobox on an Excel worksheet that lists the months January, February, March, etc.   The combobox references a range of cells (let's say B1 through B12) to be able to display the values to choose from.

I want to be able to do this without a macro.

If the month selected from the combobox is the current month or a future month, I want to display an error.

So, for example, right now we're in November.  If the user selects November, I want to display a message like "Invalid month" or something.   It's not the displaying of a message I'm having trouble with. It's how to get the program to determine that November is equal to the current month or that December (if that month was selected) is greater than November, etc.

In other words, if I was using numeric values for the months (January being "1", February being "2", etc), it would be easy to determine greater than, equal to, less than etc.   But since I'm using text for the months, I don't know how to make the comparison.

Thanks
0
I had this question after viewing How to get the same result given by formula vba Scripting Dictionary?.


Rgonzo1971 was so kind helping me, wrapping the formula into Evaluate method to output the result in a cell.

i used debug.print timer in the begging of my code and at the end, for many formulas like this. it takes quite a lot of time.

i was wondering if there is any easy solution with scripting dictionary to extract the same result but not using the formula.

if it is not possible or takes too much time, then i can live with this, otherwise if it is possible for scripting dictionary then it will really make the code faster.   for just one formula, i can replicate for my other formulas. all i need is just for this one formula.

I am not good at coding, but i found use of scripting dictinary very effective and fast.
for example,  VBA to remove duplicate with formula was too slow and with scripting dictionary was many time faster than formula method.

thanks very much for your help.
EE.xlsb
0
Hello.

Wondering if cell color can be controlled using a function instead of a macro.  Specifically what I need is to turn the cell fill color yellow when a data entry error is made by a user.  Here is my existing function which is in Cell C3:

=IF(SUM(B3-A3)>0,"Error","")

In addition to displaying "Error", which it already does, I want to modify the function to turn cell C3 yellow when an Error is made (i.e. when the value in B3 is greater than the value of A3)

Thank you
0
how can I lookup with two values in Excel. Please read sheet 2 from sheet 1 in the attached example.
grid-map-analysis.xlsx
0
I need a macro that
1. moves the active cell up one row from the current location.
Tests for cell contents. Does this cell contain any numbers or alphanumeric characters?
if yes
     - move down one row and type the word "Filled"
     - move one column to the right
     - message box "Do you want to continue Yes/No"
          if yes, go to step 1 above
          if no exit procedure.

If no,
move down one row
Move one column to the right.
Exit procedure
0
Hi,
I am having problems trying to copy a range using the curly brackets.
One effort displayed the formula in text format.
I cannot seem to follow the website instructions and maybe someone can
walk me through it.
Many Thanks
Ian
0
Concerto Cloud for Software Providers & ISVs
LVL 5
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Hi,
Is it possible to increase the font size on the formula bar without affecting the cells font size  ?
If so how ?
Thanks
Ian
0
how can I change the format of the attached sheet 1 to sheet 2

Regards,

Dallag
Change_Sheet1_Sheet2.xlsx
0
If you look at the Orography tab in the attached file there is a graph. The blue line represents a hill or ridge. The dots represent the position of a scaffold on the hill. On the left is the upwind side and there is an orange line. If the scaffold is above this line then it is affected by the terrain and a set of equations need to be checked. I want to shade above the line. On the right is the downwind side. I want to shade below the grey line down to blue. I can introduce dummy nodes to place a node on the blue line directly under the grey line node. I can do the same the other side at 0,900. I cannot find how to shade these areas?
graph.xlsx
0
Dear Experts,

Please guide me on Following Formula in Countif. i have Range Range A1:A5 = "F" and B1:B5 = "M and Criteria is at C1 = "FM" i am Using Formula Countif(A1:A5&B1:B5,C1) ans must be 5 but not working with Single enter or Array Formula Ctrl+Shif+Enter Please guide how to figure it out.

Regards,
0
Rows 1 to 73  show what I am trying to do.

1. Move the contents  of the row below the call sign in Column B, or in Column C, (the columns are not regular) into Column O, on the the same row as the call sign from the row immediately above.
2 Where there is a suburb in column A, copy this name into each second row below, onto the same row as the call sign in column B, until there is another suburb.

I'll be able to tidy up the rest.

Thanks.
2017-TV-by-suburb-and-call-sign.xlsx
0
Column A contains the New ID number.
Column B contains the Old ID number.

In many cases, there are several New ID numbers for one repeated Old ID number.
And several repeated Old ID number for one New ID number.

There are a several long lists of Old ID from different files that need to be matched to New ID.

The concept is that a list of Old ID could be pasted into Column F, and  column E could repeat the same Old ID as often as it appears in Column B with the matching New ID from Column A, shown in column D - manual example shown in file.

Hope this makes sense.
Example-Old-ID-match-New-ID.xlsx
0
Hello,

This is a follow-up question to a previous thread located here:

Auto-fill a cell's color based on numeric RGB values and vice versa in Excel

In that thread, the solution (by Ejgil Hedegaard) includes an attached Excel file in which the fill color of a particular cell, automatically changes in response to entering various values in three other cells labeled R (red), G (green), & B (blue).

This functionality is tremendously useful for me (and I suspect will be for others) because it enables you to instantly view the new color resulting from changes in any of the RGB parameters (ie without the necessity of the four clicks typically required to obtain that information):

        Home > Fill Color > More Colors > Custom

(The file also includes a chart for quickly converting from RGB decimal to RGB hex and vice versa.)

Additionally, the solution to the thread contains the formula for calculating Excel's color code:

        ExcelColor = R + G *256 + B *256^2

For example:

        Red = 204
        Green = 102
        Blue = 255

is calculated as follows:

        ExcelColor = 204 + (102*256) + (255*256^2)
        ExcelColor = 16737996

It also displays the following for reversing the process (ie :

        R = C Mod 256
        G = C \ 256 Mod 256
        B = C \ 256^2 Mod 256

Are these three formulas math or code and what is the purpose of the backslash?
0
I have a list of PST files in Excel (with their full path)

a VBA running within excel would  open each PST file (load it) into Outlook and then count the items in each folder and close it.
It would then write in a cell the information for the PST and proceed with the others, until exhaust the list.

At least for now,  I need just a hint on how access the current outlook session (I now how to start a new one from Excel, but it would much more efficient to use an already open instance of Outlook)
0
Error received from VB when I submit my codeSnippet of the linked Excel tables I need to update in AccessError received from VB when I submit my codeGood day to all! I am literally at wit's end with attempting to create a VBA code that works to update linked Excel linked tables in Access. Let me provide some background. Attached is a screenshot of just SOME of the linked Excel tables in Access that I need to create a new field for. There are a total of 40 Groups and at 11 SELECT tables per group, you do the math that is A LOT of tables to update manually (440= 11 x 40), that is not only cumbersome, but it is painful! There has to be a more automated way!

Naturally, I would have submitted an ALTER command with ADD Column, and be done with it! But as you may know, in Access 2002, MS took away the benefit to modify linked Excel tables. So that door closed on me.

I have turned to VBA. Not that great at it, SQL is more my thing, but apparently there is literature out there to perform such task, the one I am trying to do via VBA. And as I am getting more exposed to VBA, I am getting the gist of it. I believe I created a VBA code that will do the job (see below), but when I perform the VBA, I get  Run-time error '2147217900 (8004e14):
Syntax error in ALTER TABLE statement.
Now what am I doing wrong? Can someone please help? Further, once this problem is involved, can someone please show me how to do mass ALTER TABLE statements within VBA, please? That way I can get through this painstaking task? :-( In SQL, I would have been able to do the mass statements …
0
What does it mean to be "Always On"?
LVL 5
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

I need to extract data from a table on a web page but i don't know what to call it. I inherited a workbook which does this for another site and identifies the table as "1". Please take a look at this page and let me know if possible what to call the Activity Log table (about halfway down the page). Ideally I want to retrieve the data under PAST FLIGHTS.

"http://flightaware.com/live/flight/A6ETA"

.WebSelectionType = xlSpecifiedTables
.WebTables = "1"

Open in new window


Thanks!

John
0
I can open access fine using the following command line but, i cannot get the syntax for it to connect in excel through any of the wizards.  Any help would be appreciated-


"C:\Program Files\Microsoft Office 15\root\office15\msaccess.exe" "\\srv05\database.mdb" /wrkgrp "\\srv05\folder2\jobboss.mdw" /user username /pwd password123
0
I just placed a combobox that lists the 12 months of the year on an Excel worksheet.  The user is to select the applicable month.  Is there a way to reference the selection so that it appears in the middle header section when printing the worksheet?   Hopefully it can be referenced in some way without having to set up a macro.

Thanks
0
I'm developing an Excel file for data entry by users.  The file has 12 tabs.  The data entry area in each tab is no larger than one page.  So, if I was to print the entire file, it would be 12 pages long.

I'm trying to figure out a way to force the users to select the month the data pertains to and have the month appear in the center header of each sheet.  Multiple months across the tabs cannot be used.  The month must be the same on each sheet.

I'm not trying to insert the current month but rather a selected month.   For example, the users may need to generate a report for say, July, 2017.   I want the users to be able to open the file and be forced somewhere along the line (possibly before being able to print or save) to select the month the data pertains to.

Basically there are 2 or 3 users involved; each sharing the file (in sequence, not concurrently) and each responsible for a few of the tabs in the file.   If the first user selects a month, then if all tabs are populated with that selected month, that would be fine.  Or, maybe each user goes through the process of selecting the month but the program looks at the month selected by earlier users and prevents an alternate month from being selected.

Any ideas?  Thanks.
0
In the past 3 weeks, user's have received attachments from their email accounts and/or company drives which have not allowed them to open the attachments in Word, Excel and PowerPoint.  The current work around is to open the application, file, options, trust center, trust center settings, protective view and remove the check marks from all 3 boxes.  This was not an issue before but just started.  Any input on what might have caused this change or why it's occurring now?
1
Need a macro to review a range of cells and delete any with dates older than 12 months.   (And shift cells left on the row when one is deleted.)   Thanks!
0

Microsoft Excel

132K

Solutions

37K

Contributors

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.