Search:

Type: Posts; User: JackDanIce; Keyword(s):

Page 1 of 6 1 2 3 4

Search: Search took 0.28 seconds.

  1. Replies
    14
    Views
    304

    Re: VBA write data from Excel to Access

    Hi xenou - yes meant libraries, not add-ins. Same reason though, too many variable installs across User PCs and management want late binding.

    Currently, the entire data set is ~12k rows

    Set up...
  2. Replies
    14
    Views
    304

    Re: VBA write data from Excel to Access

    Changed the query to this:


    Write_Query = "INSERT INTO tbl_Data_ORIG SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & ThisWorkbook.FullName & "].[Data$]" & Data_String_Address

    Giving error...
  3. Replies
    14
    Views
    304

    Re: VBA write data from Excel to Access

    Ty welshgasman but both links rely on use of add-ins, namely:

    Microsoft ActiveX Data Objects 6.1 Library
    Microsoft Office 14.0 Access database engine objects

    Which in turn means using early...
  4. Replies
    14
    Views
    304

    Re: VBA write data from Excel to Access

    Thanks Norie, spotted that after posting, corrected it but no joy :(

    Will try with named range and reply back
  5. Replies
    14
    Views
    304

    Re: VBA write data from Excel to Access

    Including the header row doesn't resolve (same error thrown up)

    I'm not sure who I'd run this from Access, do you mean to test/resolve just the SQL query? If so, I'm not sure how to write:
    ...
  6. Replies
    14
    Views
    304

    Re: VBA write data from Excel to Access

    I've tried but same error, making me guess it's syntax related. Currently the write query reads as:



    INSERT INTO tbl_Data_ORIG SELECT * FROM...
  7. Replies
    14
    Views
    304

    VBA write data from Excel to Access

    Hi,

    Due to large amounts of data, I'm working on a system with an Excel front-end and Access as data store.

    For proof of concept/testing, I've successfully written code to read data from a...
  8. Re: Clustered column chart - highlight specific column if condition met

    Thank you both, lots to review! And much appreciated.
    Toadstool correct, I got my ranges wrong, meant A1:L1 etc
    Jon Peltier, I think "No Border & Gap Width" may be best work around or visually...
  9. Re: Clustered column chart - highlight specific column if condition met

    Hi Toadstool, thanks for replying :)

    I think I can adapt this, my dates are horizontal rather than vertical (unfortunately it's not a normalised table). Assume I can, unless I reply back with more...
  10. Clustered column chart - highlight specific column if condition met

    Hi,

    I have:

    A time line from A1:A10 (months)
    Revenue data: B1:B10 (+ve values)
    Cost of Revenue: C1:C10 (+ve values)
    Difference: D1:D10 with formula =ABS(C1-B1)<1000

    I currently use A1:C10...
  11. Replies
    9
    Views
    237

    Re: Excel 365 Macros running slow

    Thank you Steve, I wasn't aware of this issue, I only recently upgraded to Office 365. For now it is a process that can be left to run, but anticipate future faster demands :(
  12. Replies
    9
    Views
    237

    Re: Excel 365 Macros running slow

    Hi Moshim,

    Just checked, no updates available.

    However, I do understand about minimising read/write actions to the spreadsheet will speed up the execution.


    I just tried your code and it's...
  13. Replies
    9
    Views
    237

    Re: Excel 365 Macros running slow

    The GRT_1 is a named range and always 37R x 39C and there are about 40 in total, however, I'll try the debug.print suggestion, thank you.

    I just paused it and it's been running for about 3 hours...
  14. Replies
    9
    Views
    237

    Re: Excel 365 Macros running slow

    Thank you for posting, it's not code optimisation though, it's the same code I had last week that ran much much faster.

    Now it's running very slowly and unclear why. Right now code is still...
  15. Replies
    9
    Views
    237

    Re: Excel 365 Macros running slow

    Additionally, file is saved to desktop, opened in app and autosave is disabled.

    Ran twice just now with run-times: 50 seconds, 1min 5 seconds

    Output is 41 rows x 3 columns, not a large range
  16. Replies
    7
    Views
    152

    Re: VBA Copy Non Contiguous Columns

    Try:

    Sub Copy

    Dim LR as Long
    LR = Application.Max(Cells(Rows.Count, 2).End(xlUp).Row, Cells(Rows.Count, 4).End(xlUp).Row)

    Range(Replace("B2:B@,D2:D@", "@", LR)).Copy...
  17. Replies
    9
    Views
    237

    Excel 365 Macros running slow

    Hi,

    Since yesterday, I've noticed a number of macros running slower than before. I was using Excel 2013 and about a month ago upgraded to Office.365

    I have a range A1:AK39 where

    A1 = Table...
  18. Thread: VBA Loop

    by JackDanIce
    Replies
    4
    Views
    84

    Re: VBA Loop

    You're welcome
  19. Thread: VBA Loop

    by JackDanIce
    Replies
    4
    Views
    84

    Re: VBA Loop

    Try:


    Sub m1()

    Dim LastRow As Long
    Dim x As Long
    Dim y As Long
    Dim z As Long: z = 1
  20. Re: Copy/Paste from one workbook to another 2 workbooks with range

    The VBA project is locked so can't determine what test33.xlsm is doing nor can interact with the code to see why it doesn't open files.
  21. Replies
    13
    Views
    377

    Re: copy based on NAMES instead of ID

    You're welcome, glad it's resolved
  22. Replies
    13
    Views
    377

    Re: copy based on NAMES instead of ID

    Not sure what you mean, but perhaps:


    r.Resize(, uBound(b)).value = b
    r.Offset(1).Resize(, uBound(a)).value = a

    r is the cell you are writing contents of array b to, then your code is...
  23. Re: Copy/Paste from one workbook to another 2 workbooks with range

    It works for me when I mock some test files up. Without your files, I can't determine the cause of error.
    [/COLOR]
  24. Re: Copy/Paste from one workbook to another 2 workbooks with range

    Is the destination sheet named Sheet1 in the workbook that is open?

    If this gives the same error, without your files difficult to diagnose:


    Private Sub CommandButton1_Click()

    ...
  25. Replies
    13
    Views
    377

    Re: copy based on NAMES instead of ID

    [Untested] Replace all of the code with below and try:


    Sub LTF()

    Dim sFolder As String
    Dim sFile As String

    With Sheets("copy").Cells(2, 11)
    If Len(.Value) > 0...
Results 1 to 25 of 150
Page 1 of 6 1 2 3 4