Results 1 to 1 of 1
  1. #1
    Join Date
    Aug 2011
    Posts
    1

    Unanswered: Need help...Excel Macro to query SQL problem

    Hello all, Please help?

    Trying to automate an activity report, that queries a SQL database for activity between today at 6am and yesterday at 6am. Want to dynamically pass the dates to SQL so the report runs completely hands free.

    This code was obtained through a recorded macro.... Actually connection data withheld for security reasons.

    .CommandText = Array( _
    "SELECT viewBatchMaterialDetail.BatchRowID, viewBatchMaterialDetail.Date_Time, viewBatchMaterialDetail.OrderID, viewBatchMaterialDetail.BatchNumber, viewBatchMaterialDetail.RecipeID, viewBatchMaterialD" _
    , _
    "etail.MaterialID, viewBatchMaterialDetail.MaterialName, viewBatchMaterialDetail.ContainerID, viewBatchMaterialDetail.TargetWeight, viewBatchMaterialDetail.ActualWeight, viewBatchMaterialDetail.Toleran" _
    , _
    "ceWeight, viewBatchMaterialDetail.ToleranceCode, viewBatchMaterialDetail.StepNumber, viewBatchMaterialDetail.TransferSystemName, viewBatchMaterialDetail.TransferSystemID, viewBatchMaterialDetail.Desti" _
    , _
    "nationContainerID, viewBatchMaterialDetail.MixTime, viewBatchMaterialDetail.UserWhoEnteredRecipe, viewBatchMaterialDetail.UserWhoSetTransferPath, viewBatchMaterialDetail.BatchWeightTarget, viewBatchMa" _
    , _
    "terialDetail.BatchWeightActual" & Chr(13) & "" & Chr(10) & "FROM LASI_Mill.dbo.viewBatchMaterialDetail viewBatchMaterialDetail" & Chr(13) & "" & Chr(10) & "WHERE (viewBatchMaterialDetail.Date_Time>{ts '2011-08-30 06:00:00'} And viewBatchMaterialDetail.Date" _
    , "_Time<{ts '2011-08-31 06:00:00'})")
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .ListObject.DisplayName = "Table_Query_from_Mill_log_data"
    .Refresh BackgroundQuery:=False

    Thank you...
    Last edited by gwykert; 08-31-11 at 11:42.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •