This article will guide you on how to include a URL link for the Excel file used to upload records to NetSuite.
The video will show you how it works for Journal Entry but can be applied to any other record type.
Prerequisites
In NetSuite, create a custom field for the Hyperlink field. Follow the steps here.
Excel Developer Tab, click here to see instructions to activate.
MUST be connected to an Exchange server or Microsoft 365 using a direct connection.
Making a Hyperlink back into Excel Workbook
Step 1: Go to NetSuite and create a custom field for the hyperlink field. Follow the steps here.
Step 2: Open a new Excel workbook, create a template for the record type you want to use, and include the custom hyperlink field. We will be using Journal Entry for this example.
Step 3: Highlight the hyperlink column and go to Home then convert format type to General.
Step 4: Click File> Save As. Enter the name of your template in the file name box then save the file as a Macro-Enabled template and stored to OneDrive or SharePoint.
Step 5: You can use VBA or the formula below and paste into your Excel file (convert the cell format to General.
=LEFT(SUBSTITUTE(@CELL("filename",A1)," ","%20"),FIND("[",SUBSTITUTE(@CELL("filename",A1)," ","%20"))-1) & MID(SUBSTITUTE(@CELL("filename",A1)," ","%20"),SEARCH("[",SUBSTITUTE(@CELL("filename",A1)," ","%20"))+1, SEARCH("]",SUBSTITUTE(@CELL("filename",A1)," ","%20"))-SEARCH("[",SUBSTITUTE(@CELL("filename",A1)," ","%20"))-1) &"?web=1"
or
If you want to use VBA
Go to the Developer tab and click Visual Basic.
Copy and paste the code
Copy and paste the code
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim cell As Range
'Check if the changed cell is within Column D (Tran Date)
If Not Intersect(Target, Me.Columns("D")) Is Nothing Then
Application.EnableEvents = False 'Disable event handling to prevent infinite loop
'Loop through each changed cell in Column D
For Each cell In Intersect(Target, Me.Columns("D"))
'Check if the cell is not empty
If cell.Value <> "" Then
'Calculate the formula and assign it to the corresponding cell in Column I
cell.Offset(0, 5).Value = "=LEFT(SUBSTITUTE(@CELL(""filename"",A1),"" "",""%20""),FIND(""["",SUBSTITUTE(@CELL(""filename"",A1),"" "",""%20""))-1) & MID(SUBSTITUTE(@CELL(""filename"",A1),"" "",""%20""),SEARCH(""["",SUBSTITUTE(@CELL(""filename"",A1),"" "",""%20""))+1, SEARCH(""]"",SUBSTITUTE(@CELL(""filename"",A1),"" "",""%20""))-SEARCH(""["",SUBSTITUTE(@CELL(""filename"",A1),"" "",""%20""))-1) &""?web=1"""
'Wrap text in the corresponding cell in Column I
cell.Offset(0, 5).WrapText = True
Else
'Clear the corresponding cell in Column I if Column D cell is cleared
cell.Offset(0, 5).ClearContents
End If
Next cell
Application.EnableEvents = True 'Re-enable event handling
End If
End Sub
Step 6: Create and Upload Record
NetSuite Output
How to Create a Custom field for the Hyperlink in NetSuite (For NetSuite Admin)
Step 1: Go to Customization, Lists, Records, & Fields > Transaction Body Fields > New
Step 2: Populate the necessary fields, and make sure that the field type is set to Hyperlink.
Step 3: Set the role of the user to have permissions to Custom Body Fields. See the full list of permissions here.
Set Default Template File Location
To set the folder path where you want your personal templates to saved.
Step 1: Open Excel >File > Save.
Step 2: Check the Default Personal Location, this should be the Custom Office Template directory.
Click here to see the steps to locate the Custom Office Template folder:
Click here to see the steps to locate the Custom Office Template folder:
Go to This PC >Documents then click Custom Office Templates. Copy the file directly into the URL and paste it to the Default personal template location.
or
Windows search bar type Custom Office Templates then right-click on the folder to copy the path.
Activate Excel Developer Tab
Step 1: Go to File > Options> Customized Ribbon.
Step 2: Under Customized Ribbon and Under Main Tabs, tick off Developer.
If you are not seeing it, add Developer to the Main tab from the list on the left box.
