Search results

Hi All, I have fronend and backend database. I have all data stored in another database (backend) and that database is password protected. I need to export some selected data into Excel which is created in the same process. Below is the code: Private Sub ExportLeaversList(strWorkbook As...

Hi I'm creating Excel File Dashboard from MS Access VBA and trying to setup the page size to A3 using below code: Sub SetUpPage(ws As Worksheet, Heading As String, Orient As String) On Error GoTo ErrHand Dim myOrient As String ws.Application.ScreenUpdating = False With ws...

Hi Bob Yes I entered password manually and when I tried running the same code again then its asking for the password I'll try to run the code with "

I have changed this to True but still asking for the password. objApp.DoCmd.TransferDatabase Access.AcDataTransferType.acExport, _ "Microsoft Access", _ strDestDatabasePathNName, _...

Hi All, I have table with following Columns: Banker Status Week1 Week2 Week3 Week4 ...... Week48 Week49 Week50 Budget YearMonth I'm need to extract the value only for few columns dynamically from result set. I have written following code: i = 0 strColumnsSQL = "Week" &...

This needs to be done on given range (multiple lines) on based of value in the cell

Hi I want to change the Font Colour on based of some condition e.g. If number is 100% and above then change numbers colour to Green If it's above 95% and Less than 100% then Amber If it's less than 95% then Red And this need to be changed for a range e.g. D1:D20 I'm not sure how to apply...

I have managed to do that using following code Private Sub ProcessWeeklyData() On Error GoTo ErrHandler Dim rsTmp As DAO.Recordset Dim strInsertSQL As String Dim strSelectSQL As String Dim fName As Field Dim qryWklyDashboardData As String Dim strField1...

Thanks Isskint. I'm new to Access VBA Can you pleae give me a sample code for this. Appreciate your help

Hi I want insert the result of below query in one table, any idea how to achieve this? TRANSFORM Last(Nz(RevenueInUSD,0)) AS WKRevenueInUSD SELECT [Banker] FROM tbl_TB INNER JOIN tbl_Weekly_Calendar ON tbl_TB.ValBookDate=tbl_Weekly_Calendar.ReportingDate WHERE [BU]='Asia' And...

I'll prefer to remove from the result set only but now sure how to do that? I please send me the sample for the same. I don't want use TransferSpreadsheet Method because there are number of quierie's results need to be populated into Excel Dashboard into specific cells and need to do the...

Hi Paul, How to remove from the rsTmp? rsTmp contains full records with 20 columns. Do I need each columns one by one or there is some syntex to check sum of all the columns? Can you please send me the syntex? Thanks

Hi All, I am writing data from Access to excel. There are few records with 0 values. How to remove those records? Below the code used to export data from Access to Excel. Dim TempString As String Set objXLApp = CreateObject("Excel.Application") wb = "F:\MyDoc\Weekly MI Automation\" &...

Hi Rx, Please find below the code used. Dim TempString As String Set objXLApp = CreateObject("Excel.Application") wb = "F:\MyDoc\Weekly MI Automation\" & sTeamRegionName & Left(Date, 2) & Mid(Date, 4, 2) & Right(Date, 4) & "_" & Left(Time, 2) & Mid(Time, 4, 2) & ".xlsx"...

Hi Rx, I'm getting below error: Compile error: Method or data member not found. objXLSheet.range("RowEnd").Select objXLSheet.ActiveCell.Formula = "= SUBTOTAL(101, B" & RowEnd & ":B" & recount + 6 & ")"

Thanks... Did silly mistake

Please note I have tried CInt, CSng and CDbl e.g. Select MAX(CINT([YearMonth])) from tbl_Revenue_Assets_Split GROUP BY CINT([YearMonth]) Error: Overflow

Hi How to get MAX YearMonth Value using SQL query in MS Access 2007. Pls note YearMonth is stored as Text I'm executing below SQL and it's returning all the records from the table Select MAX([YearMonth]) from tbl_Revenue_Assets_Split GROUP BY [YearMonth] Output is coming: YearMonth...

Hi I'm using following method to populate the data in Excel Sheet For count = 1 To recount ColumnStart = 1 objXLSheet.Cells(RowStart, ColumnStart) = rsTmp![Banker] ColumnStart = ColumnStart + 1 objXLSheet.Cells(RowStart, ColumnStart) =...

Thanks ... Solved...I forgot "Dim" while declaring the variable