Connect with experts and redefine what’s possible at work – join us at the Microsoft 365 Community Conference May 6-8. Learn more >

developer

1264 Topics

Excel Date Picker
Hello , I would like to add date picker on an excel sheet. The goal is to create an excel file and have this sent to users to fill in. When filling the excel, and click on the start date, a calendar dropdown should appear and users select the date from the calendar so as to eliminate different date format and make it unique across the document. I was able to get a date picker add-in however this wont work because I am sending to users please see below:
MVPromise
Sep 19, 2020 Place Excel
632KViews
0likes
13Comments
REMOVE A CHECKBOX FROM EXCEL WORKSHEET
I was handed an Excel worksheet that has three checkboxes that I wish to remove but cannot seem to remove them. Excel Help says to right click the Box and then hit Delete. There is no Delete in the ensuing dropdown menu and Delete on the keyboard does not work. Any suggestions?
tjbCanton2020
Dec 03, 2020 Place Excel
319KViews
4likes
23Comments
Excel to XML
I need to convert Excel to XML. I have the XML Schema and data available. Any idea where to start?
Solved
Fitness__69
Mar 22, 2023 Place Excel
142KViews
0likes
7Comments
SOLVED - "Method 'SaveAs' of object '_Workbook' failed" (1004) when saving into same file location
Hello, I have code designed to create an archive of my main sheet (as a .xlsx) by copying the sheet > saving the copied sheet in a new workbook > doing things to the sheet within the new workbook > saving and closing new workbook then continuing the rest of my code. Everything works as coded except when the user selects (or keeps selected) the same file location, in the SaveAs dialog, that the original file (with the running VBA) is in. It returns a "Method 'SaveAs' of object '_Workbook' failed" error. I created an "If" check to see if the selected file location from the SaveAs dialog is the same as the file location of the original and was able to create an error handler (avoid the error), but not an error solution. I want to default to the same file location as the original, and regardless I want the user to be able to save into the same file location, especially since that is a very typical thing to do. Line (59) with error 1004: ActiveWorkbook.SaveAs fileName:=PathAndFile_Name, FileFormat:=xlOpenXMLWorkbook ShiftYear (what code is in) and PleaseWait are userforms, and "Troop to Task - Tracker" is the sheet I'm copying. Code with error: '<PREVIOUS CODE THAT DOESN'T PERTAIN TO THE SAVEAS ISSUE> '''Declare variables: 'General: Dim NewGenYear As Integer, LastGenYear As Integer, year_create_counter As Integer NewGenYear = 0: LastGenYear = 0: year_create_counter = 0 'Personnel: Dim cell_person As Range, cell_num As Range Dim cell_num_default As Range 'Archive: Dim Sheet_Archive As Worksheet, ShVal As Integer Dim ObFD As FileDialog Dim File_Name As String Dim PathAndFile_Name As String Dim Shape_Clr As Shape Dim cell_color_convert As Range '<A WHOLE BUNCH OF OTHER CHECKS AND CODE THAT DOESN'T PERTAIN TO THE SAVEAS ISSUE> 'Set then launch SaveAs dialog: If ShiftYear.CheckBox5.Value = True Then 'Archive <=5 year(s) data externally - Checked: For Each Sheet_Archive In ThisWorkbook.Sheets Select Case Sheet_Archive.CodeName Case Is = "Sheet4", "Sheet5", "Sheet6", "Sheet7" ShVal = Sheet_Archive.Name If Sheet_Archive.Range("A2").Value <> "N/A" And ShVal <> ShiftYear.Shift_3.Value Then File_Name = "Archive " & Sheet_Archive.Name & "_" & ThisWorkbook.Name 'Set default (suggested) File Name Set ObFD = Application.FileDialog(msoFileDialogSaveAs) With ObFD .Title = "Archive Year(s) - Personnel Tracker" .ButtonName = "A&rchive" .InitialFileName = ThisWorkbook.Path & "\" & File_Name 'Default file location and File Name .FilterIndex = 1 'File Type (.xlsx) .AllowMultiSelect = False .InitialView = msoFileDialogViewDetails .Show If .SelectedItems.count = 0 Then MsgBox "Generation and archiving canceled. No year(s) were created, shifted, or overwritten. To continue generating without archiving, uncheck the ""Archive <=5 year(s) calendar/personnel data externally before overwriting"" box then click ""Generate"" again." _ , vbExclamation, "Year Shift & Creation - Personnel Tracker" '<MY CODE THAT TURNS OFF MACRO ENHANCEMENT> Exit Sub Else PathAndFile_Name = .SelectedItems(1) End If End With Application.DisplayAlerts = False 'Load year to be archived: Worksheets("Formula & Code Data").Range("I7").Value = Sheet_Archive.Name Worksheets("Formula & Code Data").Range("I13").Value = "No" Call Load_Year.Load_Year 'Copy Troop to Task - Tracker sheet into new workbook and format: PleaseWait.Label2.Caption = "Creating " & Sheet_Archive.Name & " archive file ..." DoEvents File_Name = Right(PathAndFile_Name, Len(PathAndFile_Name) - InStrRev(PathAndFile_Name, "\")) 'Update File Name to user's input ThisWorkbook.Sheets("Troop to Task - Tracker").Copy ActiveWorkbook.SaveAs fileName:=PathAndFile_Name, FileFormat:=xlOpenXMLWorkbook 'New workbook save and activate '<ALL MY CODE THAT CHANGES THE NEW WORKBOOK> Excel.Workbooks(File_Name).Activate Excel.Workbooks(File_Name).Close savechanges:=True 'New workbook save and close Application.DisplayAlerts = True End If End Select If (Sheet_Archive.CodeName = "Sheet4" Or Sheet_Archive.CodeName = "Sheet5" _ Or Sheet_Archive.CodeName = "Sheet6" Or Sheet_Archive.CodeName = "Sheet7") _ And ShVal <> ShiftYear.Shift_3.Value Then PleaseWait.Label2.Caption = "" & Sheet_Archive.Name & " archive file complete" DoEvents Else: PleaseWait.Label2.Caption = "Initailizing archive ..." DoEvents: End If Next Sheet_Archive ElseIf ShiftYear.CheckBox5.Value = False Then 'Archive <=5 year(s) data externally - Unchecked: 'Do Nothing End If 'Archive <=5 year(s) data externally - END '<CONTINUING CODE THAT DOESN'T PERTAIN TO THE SAVEAS ISSUE> Code with error handler: '<PREVIOUS CODE THAT DOESN'T PERTAIN TO THE SAVEAS ISSUE> '''Declare variables: 'General: Dim NewGenYear As Integer, LastGenYear As Integer, year_create_counter As Integer NewGenYear = 0: LastGenYear = 0: year_create_counter = 0 'Personnel: Dim cell_person As Range, cell_num As Range Dim cell_num_default As Range 'Archive: Dim Sheet_Archive As Worksheet, ShVal As Integer Dim ObFD As FileDialog Dim File_Name As String Dim PathAndFile_Name As String Dim Shape_Clr As Shape Dim cell_color_convert As Range '<A WHOLE BUNCH OF OTHER CHECKS AND CODE THAT DOESN'T PERTAIN TO THE SAVEAS ISSUE> 'Set then launch SaveAs dialog: If ShiftYear.CheckBox5.Value = True Then 'Archive <=5 year(s) data externally - Checked: For Each Sheet_Archive In ThisWorkbook.Sheets Select Case Sheet_Archive.CodeName Case Is = "Sheet4", "Sheet5", "Sheet6", "Sheet7" Archive_Error: ShVal = Sheet_Archive.Name If Sheet_Archive.Range("A2").Value <> "N/A" And ShVal <> ShiftYear.Shift_3.Value Then File_Name = "Archive " & Sheet_Archive.Name & "_" & ThisWorkbook.Name 'Set default (suggested) File Name Set ObFD = Application.FileDialog(msoFileDialogSaveAs) With ObFD .Title = "Archive Year(s) - Personnel Tracker" .ButtonName = "A&rchive" .InitialFileName = ThisWorkbook.Path & "\" & File_Name 'Default file location and File Name .FilterIndex = 1 'File Type (.xlsx) .AllowMultiSelect = False .InitialView = msoFileDialogViewDetails .Show If .SelectedItems.count = 0 Then MsgBox "Generation and archiving canceled. No year(s) were created, shifted, or overwritten. To continue generating without archiving, uncheck the ""Archive <=5 year(s) calendar/personnel data externally before overwriting"" box then click ""Generate"" again." _ , vbExclamation, "Year Shift & Creation - Personnel Tracker" '<MY CODE THAT TURNS OFF MACRO ENHANCEMENT> Exit Sub Else PathAndFile_Name = .SelectedItems(1) End If End With Application.DisplayAlerts = False 'Load year to be archived: Worksheets("Formula & Code Data").Range("I7").Value = Sheet_Archive.Name Worksheets("Formula & Code Data").Range("I13").Value = "No" Call Load_Year.Load_Year 'Copy Troop to Task - Tracker sheet into new workbook and format: PleaseWait.Label2.Caption = "Creating " & Sheet_Archive.Name & " archive file ..." DoEvents File_Name = Right(PathAndFile_Name, Len(PathAndFile_Name) - InStrRev(PathAndFile_Name, "\")) 'Update File Name to user's input ThisWorkbook.Sheets("Troop to Task - Tracker").Copy If PathAndFile_Name = ThisWorkbook.Path & "\" & File_Name Then 'Error handler Archive_Error_Actual: MsgBox "You cannot save into the same location as this Tracker, in this version. Please select a different file location." _ , vbExclamation, "Year Shift & Creation - Personnel Tracker" 'UPDATE MESSAGE AND FIGURE OUT WAY TO FIX RUNTIME ERROR WHEN SAVING TO SAME LOCATION AS THE TRACKER!!! ActiveWorkbook.Close savechanges:=False GoTo Archive_Error End If On Error GoTo Archive_Error_Actual ActiveWorkbook.SaveAs fileName:=PathAndFile_Name, FileFormat:=xlOpenXMLWorkbook 'New workbook save and activate '<ALL MY CODE THAT CHANGES THE NEW WORKBOOK> Excel.Workbooks(File_Name).Activate Excel.Workbooks(File_Name).Close savechanges:=True 'New workbook save and close Application.DisplayAlerts = True End If End Select If (Sheet_Archive.CodeName = "Sheet4" Or Sheet_Archive.CodeName = "Sheet5" _ Or Sheet_Archive.CodeName = "Sheet6" Or Sheet_Archive.CodeName = "Sheet7") _ And ShVal <> ShiftYear.Shift_3.Value Then PleaseWait.Label2.Caption = "" & Sheet_Archive.Name & " archive file complete" DoEvents Else: PleaseWait.Label2.Caption = "Initailizing archive ..." DoEvents: End If Next Sheet_Archive ElseIf ShiftYear.CheckBox5.Value = False Then 'Archive <=5 year(s) data externally - Unchecked: 'Do Nothing End If 'Archive <=5 year(s) data externally - END '<CONTINUING CODE THAT DOESN'T PERTAIN TO THE SAVEAS ISSUE> Any solution to this is much appreciated!
Solved
Kendethar
Mar 08, 2022 Place Excel
111KViews
0likes
11Comments
Need to search multiple words in a cell and get the output based on the word found.
Hi, I am having a data in column A like below, then I need search for multiple words like "Generic Mailbox", "Distribution", "Non-standard", "NSSR" in the each cell and then if "Generic mailbox" is founds in the cell, output should be "shared mailbox" or If "Distribution" is founds in the cell, output should be "DL" or If "Non-standard" is founds in the cell, output should be "Corporate request" or If "NSSR" is founds in the cell, output should be "Non-Standard Service request" Looking for to get help on this!! Thank you.
Solved
Santhosh_V
Jun 27, 2021 Place Excel
99KViews
0likes
46Comments
Button for reset all filters
Hi, First of all I am not at good at this but I looking for a way to use a button to reset all the filters I have. To use one button instead of clear all the filters separately would be great as it is more effcient. But as I wrote, I am not very good at this I was wondering if someone could show/teach me how to do it.
claym
Feb 22, 2019 Place Excel
82KViews
0likes
2Comments
Excel crashes when opening a specific (complex) .xlsb file (and all previous versions of it too)
I have a business critical Excel .xlsb file, that I have been updating/using for years (it contains about 20 sheets, and a lot of VBA code - none of which runs on opening). Yesterday, the file refused to open on 2 different work PCs, both running the same, fully updated version of Excel from an Office 365 E3 business subscription. Whilst I thought this one file may be corrupt, I have used this file for many years, with many older versions on the file saved (some of which have not been used/opened for years) - and Excel was suddenly unable to open these versions either!? When trying to open the file, Excel would simply freeze for a bit, then close with no error messages. Opening Excel in safe mode made no difference, same issue. Further info on this led me to believe the problem was with the latest version of Excel as: - opening the file on a laptop with a less recently updated version of Excel worked fine - I could preview the file fine in the Windows Explorer preview pane - by opening the file on the laptop, and saving it as an .xlsm file it worked -the .xlsm file can be opened on all PCs - NB I could NOT open the .xlsb file in Office 365 via the browser, I got an error message saying it needed to be viewed in the desktop (which of course didn't work either) So there is something in this file (and previous versions) that the latest version of Excel/Excel online does not like. NB other .xlsb files I have so far seem fine. This looks very similar to another user's issues, reported in Jan 2020 (which appears unresolved): https://techcommunity.microsoft.com/t5/excel/excel-workbook-xlsb-crashes-on-new-computer-and-latest-update/m-p/1105756 Can anyone shed any light? I now have a working .xlsm file - but I was only able to do this as I had a laptop with a different version of Excel on - if this wasn't the case then I could have been scuppered. And it makes me wary of using .xlsb files full-stop for now!
Jaspos
Mar 23, 2023 Place Excel
75KViews
0likes
17Comments
Retrieve deleted cells
Hi, my colleague accidentally deleted few columns in one of our sheets in the excel spreadsheet. We press undo until it says that there's no more undo action can be done. But the columns did not appear again. We don't have back-up of the previous data that were stored in the said columns or cells that were deleted. I hope you can help me in retrieving those cells. Thank you. Hoping for your response.
opd93bghmc
Jul 24, 2020 Place Excel
75KViews
0likes
1Comment
Excel can Convert JSON data to Table in Excel
Hi, Actually i am getting data from external source through API by using "From Web" option in excel & getting JSON data. But after getting data in excel, trying to transform that JSON data into Table by using power query. But, it is not working. JSON data is not transforming into proper table. I have tried pivot, transpose options in power query, still not working. Attached Excel for reference that what is required & what i am getting. Let me know if any solutions are available for this. Any insights are highly appreciated. Thanks Sridhar Dasari
SridharDasari
Sep 12, 2020 Place Excel
66KViews
0likes
6Comments
Matching file names in Excel column to file names in folder
Hi, I have a column in excel that contains the file names of files that are in a folder (ex: IMG_001, contacs.pdf, essay.docx). I have about 30 rows with miscellaneous file names in the column, and the folder contains over 1,000 varied files. What I'm looking to accomplish is to use the file names in the Excel column to find the file names in the folder, similar to matching file names between two spreadsheets with VLOOKUP. This will allow me to find the files without having to sift through the entire folder to find specific files or type each file name into the folder search bar. I tried searching for online for this task but only managed to find how to export file names from a folder to Excel. This task is almost the opposite, being that I already have the file names in Excel and need to find the file names in a folder. Additionally, I'm not too sure if I'm in the correct conversation community, but since I'm starting with an Excel workbook, I figured I'd start here and hopefully find direction. Please help!
Ozz_Kozz
Sep 30, 2020 Place Excel
58KViews
0likes
15Comments