Tag: Excel

footer – Header won’t appear when I open the Excel file on share point, because on the VIEW menu only the NORMAL view exists. How can I add Page Layout View

I have created an Excel file with Header and Footer and I can view the Header and Footer using the Page Layout view when opening the file in my computer,
I upload the same file on sharepoint and open it on sharepoint, I can not see the header and Footer, because only Standard ( or normal) View is available on Excel in Sharepoint.

How can I add the Page Layout view to Excel on Sharepont Excel.
I am not the admin and I am not a programmer.

Thank you!
Armin

enter image description here

excel – Returning matches in column in VBA

So I have the following data set from a worksheet:

+———+————-+———–+
| Account | Type | Value |
+———+————-+———–+
| XX | iPhone | 123 |
| XX | Samsung | 567 |
| XX | iPhone | 222 |
| BB | Samsung | 999 |
| CC | iPhone | 998 |
+———+————-+———–+

I needed to know the value for each account-type combination. So I copied account and type to another worksheet in column B and concatenated account and type. I removed the duplicated after

Now, I want to return the value for each account and type (in columns) like this.

+———–+———–+———-+————-+———-+
| Account | Account | Type | Value 1 | Value 2 |
+———–+———–+———+————–+———-+
| XX-iPhone | XX | iPhone | 123 | 222 |
| XX-Samsung| XX | Samsung | 567 | |
| BB-Samsung| BB | Samsung | 999 | |
| CC-iPhone | CC | 999 | 998 | |
+———+————-+————————+———-+

Here’s my code:

Dim Master as Worksheet, Filter as Worksheet
Dim lrow1 as Long

Set Master = Sheets("Master")
Set Filter = Sheets("Filter")

lrow1 = Master.range("A" & Rows.count).End(xlUp).row

Master.range("A2:B" & lrow1).copy
Filter.Range("B2").Pastespecial
'Copy info from Copy to Filter worksheet

Dim i as Integer, lrow2 as integer
lrow2 = Filter.Range("B" & Rows.count).End(xlUp).Row


With Filter
  For i = 2 to lrow2
    .Cells(i, 1) = .Cells(i ,2) & "-"& Cells(i, 3)
  Next
End With
'Concatenate data

Dim lrow3 As Long
lrow3 = Filter.range("A" & Rows.Count).End(xlUp).Row

Filter.Range("A2:C" & lrow3).RemoveDuplicates Columns:=Array(1), Header:=xlYes
'Remove Duplicates

Dim lrow4 as long
lrow4= Filter.Range("A" & Rows.Count).End(xlUp).row

Dim rg as range
Set rg = Filter.Range("A2:A" & lrow4)


Dim i as Integer, j as integer
i = 2
j = 3
   For Each cell in rg
     If cell = Master.Cells(i,1)& "-" & Master.Cells(i,2) Then
       cell.Offset(,j) = Master.Cells(i,3)
       i = i + 1
       j = j + 1
     End if
   Next

I can’t seem to make it work

website – Turn an excel sheet to a webpage

I have an Excel sheet that we use to calculate sales commissions. We, sometimes, share the file with individuals that we prefer they don’t see the formulas. One thought came to mind is to convert the Excel file to a webpage as a way to make it easier to share with people form outside the organization and also to protect the calculations that we do in the background by hiding the formulas. What methods or apps are there to help in achieving that, our website is on wix business.

I will be your Virtual Assistant and do Data Entry, Copy Paste and Typing work for $2

I will be your Virtual Assistant and do Data Entry, Copy Paste and Typing work

Hello!

I’m Emon, a Digital Marketer and MS word/excel expert. I will be your Virtual Assistant for Data Entry.

My Services:

  • Data Entry Offline/Online
  • Data Collection
  • Web Research
  • Data Mining and Scraping
  • MS Word Data Entry
  • MS Excel Data Entry
  • Copy Paste Data
  • MS Word to Excel
  • MS Excel to Word
  • PDF to MS Word/Excel
  • Images to MS Word/Excel
  • Google Docs/Sheets
  • Manually Typing

Why Me?

  • Best Quality and Clean Work
  • Accurate Work
  • 100% Satisfaction
  • Fast Delivery

Note: Please contact me before placing the order.

Thank you.

.

node.js – Upload Excel data to Microsoft SQL Server

I am trying to read excel data and insert it into a SQL Server database. I am using npm package mssql to do the same. Also using XLSX to read Excel data.

However I keep getting the same error

ErrorMessageToken { name: 'ERROR', event: 'errorMessage', number: 102, state: 1, class: 15, message: 'Incorrect syntax near '?'.', serverName: 'myservername', procName: '', lineNumber: 1 } }, name: 'RequestError', number: 102, lineNumber: 1, state: 1, class: 15, serverName: 'myservername', procName: '', precedingErrors: () }

My code:

const filePath = path.join(__dirname, 'demo.XLSX')
const workbook = XLSX.readFile(filePath)
const (firstSheetName) = workbook.SheetNames
const worksheet = workbook.Sheets(firstSheetName)

const rows = XLSX.utils.sheet_to_json(worksheet, {
    raw: true, // Use raw values (true) or formatted strings (false)
    header: 1, // Generate an array of arrays ("2D Array")
})

const pool = await connect({
    user: process.env.dbUser,
    password: process.env.dbPassword,
    server: process.env.dbServer,
    database: process.env.dbName,
    options: {
            encrypt: true,
            enableArithAbort: true,
    },
})

const query = `INSERT INTO TableName (Col1, Col2, Col3, Col4, Col5) VALUES ?  `
const result = await pool.request().query(query, (rows))
// also tried ...rows, ((rows)), (rows.values())
console.log(result)

Any help is much appreciated. Thank you

sharepoint rest api – How to use Excel Service (/Model) with Document ID enabled

I’m currently running SharePoint 2016 and have Document ID service enabled.
Knew that once I enable the Document ID service, documents’ URL will change from ~/DOCLIB/filename.xlsx to ~/_layouts/15/DocIDRedir.aspx?ID=BLAH. I was fine with that.

Recently got a new project that will have to use the Excel Service (/Model); so I followed the Microsoft documentation https://docs.microsoft.com/en-us/sharepoint/dev/general-development/basic-uri-structure-and-path; the example the documentation had was : ServerName/_vti_bin/ExcelRest.aspx/Docs/Documents/sampleWorkbook.xlsx/model

and of course I replaced the URL path with documentID URL like below ServerName/_vti_bin/ExcelRest.aspx/_layouts/15/DocIdRedir.aspx?ID=BLAH/Model and I got “sorry, something went wrong” error.

Does anybody using both services and know how to get around the issue that I’m having or is there a way to get default URL path and still keeping the Document ID?

Thank you.

Excel cell formatting not working for some cells when trying to write in paragraphs

I have an excel sheet and I want to write content in a cell. There are around 2-3 paragraphs and bullet points. When I using the cell formatting feature, it works properly. But once I closed that file, it get back to the default condition. I am attaching both conditions here.enter image description here

excel – VBA inverte dia com mês nas variáveis do tipo Date

Contexto: tenho o seguinte código que recebe do usuário uma data:

dataAtualDefault = Day(Date) & "/" & Month(Date) & "/" & Year(Date)
dataInsercao = Application.InputBox("Data da entrada", "Entrada", dataAtualDefault)

Problema: caso o usuário cometa um typo e insira uma data em que o mês seja maior que 12, como, por exemplo, 09/15/2020, o valor do mês é invertido com o do dia, e a variável passa a ter o seguinte valor: 15/09/2020

Soluções que já tentei mas não deram certo:

  1. Essa não deu certo porque o mês e dia são invertidos antes do If
    If(Month(dataInsercao ) > 12) Then
        MsgBox("Erro!")
    End If
  1. Format não me ajuda pois converte para uma String, mas eu realmente preciso que a variável seja do tipo Date
Format(dataInsercao , "dd/mm/yyyy")