Naslednja številka računa - novice

Microsoft Excel ponuja številne predloge računov, ki jih lahko prenesete. Vendar ni vgrajenega načina za povečanje na naslednjo številko računa.

Posnel sem ta video, v katerem je prikazano, kako v delovno knjigo dodate nekaj vrstic kode VBA, tako da lahko vsak račun shranite kot novo datoteko. Nato makro izbriše račun in na številko računa doda 1.

Z 166.000 pogledi in stotimi komentarji ugotavljam, da se vedno znova pojavljajo ista vprašanja. Postati nepraktično prositi ljudi, naj preberejo 800 komentarjev, ker je bil odgovor na njihovo vprašanje objavljen že šestkrat. Torej - za priljubljena vprašanja tukaj objavljam kodo.

Pogosta vprašanja # 1

Ali lahko vnesete kodo zame, ker ne morem tipkati?

Sub NextInvoice() Range("E5").Value = Range("E5").Value + 1 Range("A20:E39").ClearContents End Sub Sub SaveInvoiceWithNewName() Dim NewFN As Variant ' Copy Invoice to a New Workbook ActiveSheet.Copy NewFN = "C:aaaInv" & Range("E5").Value & ".xlsx" ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook ActiveWorkbook.Close NextInvoice End Sub

Pogosta vprašanja # 2

Račun želim shraniti kot PDF v računalniku z operacijskim sistemom Windows

Opomba

Ta koda deluje samo v različicah sistema Windows 2010 programa Excel 2010 ali novejših. Obstaja drugačna koda za Mac.

Izbrati morate obseg, ki vsebuje račun, in narediti Postavitev strani, Območje tiskanja, Nastavitev območja tiskanja. Če preskočite ta korak, se bodo na računu pojavili gumbi za zagon makra!

Sub SaveInvoiceAsPDFAndClear() Dim NewFN As Variant NewFN = "C:aaaInv" & Range("E5").Value & ".pdf" ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=NewFN, _ Quality:=xlQualityStandard, IncludeDocProperties:=True, _ IgnorePrintAreas:=False, OpenAfterPublish:=False Range("E5").Value = Range("E5").Value + 1 Range("A20:E39").ClearContents End Sub

Pogosta vprašanja # 3

Račun želim shraniti kot datoteko Excel in PDF v drugo mapo

Sub SaveInvoiceBothWaysAndClear() Dim NewFN As Variant ' Create the PDF First NewFN = "C:aaaPDFInvoicesInv" & Range("E5").Value & ".pdf" ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=NewFN, _ Quality:=xlQualityStandard, IncludeDocProperties:=True, _ IgnorePrintAreas:=False, OpenAfterPublish:=False ' Next, Save the Excel File ActiveSheet.Copy NewFN = "C:aaaInv" & Range("E5").Value & ".xlsx" ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook ActiveWorkbook.Close ' Increment the invoice number Range("E5").Value = Range("E5").Value + 1 ' Clear out the invoice fields Range("A20:E39").ClearContents End Sub

Pogosta vprašanja # 4

Številka mojega računa ima številke in črke

Kodo boste morali prilagoditi. Tu je nekaj primerov. Heidi ima številko računa, kot je SS15001. Ko pogledam to številko računa, je to dvočrkovna predpona, ki ji sledi 5 številk. Nova številka računa mora uporabljati LEVO (, 2) in MID (, 3,5):

Range("E5").Value = Left(Range("E5").Value, 2) & 1 + Mid(Range("E5").Value, 3, 5)

Tu je bolj zapleten primer. Številka računa je IN-1234-HA, kjer IN- pomeni račun. 1234 je zaporedna številka. HA je prva črka imena stranke, ki jo najdemo v B10.

LeftPart = Left(Range("E5").Value, 3) MidPart = Left(Range("E5").Value, 4, 4) + 1 EndPart = Left(Range("A10").Value, 2) Range("E5").Value = LeftPart & MidPart & EndPart

Zgornje štiri vrstice lahko poenostavite na naslednji način:

Range("E5").Value = Left(Range("E5").Value, 3) & Left(Range("E5").Value, 4, 4) + 1 & Left(Range("A10").Value, 2)

Pogosta vprašanja # 5

V delovnem zvezku imam druge makre (na primer SpellNumber) in tudi makre moram shraniti.

Strategija je nekoliko drugačna, če želite omogočiti zagon drugih makrov, da funkcija Numbers-to-Words še naprej deluje. Namesto da v nov delovni zvezek kopirate samo list z računom in uporabite SaveAs, si boste (a) zapomnili pot in ime datoteke delovnega zvezka, (b) s SaveAs shranili celoten delovni zvezek s številko računa v imenu, (c ) Izbrišite izvirni delovni zvezek. (d) Uporabite SaveAs, da shranite delovni zvezek z izvirnim imenom.

Sub SaveInvoiceWithNewName() Dim OrigFN as Variant Dim NewFN As Variant ' Remember the original path and file name OrigFN = ThisWorkbook.FullName NewFN = "C:aaaInv" & Range("E5").Value & ".xlsx" ' Save a copy with the new name ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook ' Delete the original workbook so you can save without warning On Error Resume Next Kill (OrigFN) On Error Goto 0 ' Save again as the original file name ActiveWorkbook.SaveAs OrigFN, FileFormat:=xlOpenXMLWorkbook NextInvoice End Sub

Pogosta vprašanja # 6

Zaščititi moram delovni list, da lahko moji zaposleni spremenijo le nekatere celice. Ko zaženem vaš makro, dobim "Celica, ki jo želite spremeniti, je zaščitena in zato samo za branje"

You can unprotect the sheet in the macro, write the new invoice number, and then protect the sheet. This only has to be done in the NextInvoice macro. The other macro is not making changes to any cells. Here is the code if you are using protection without a password:

Sub NextInvoice() ActiveSheet.Unprotect Range("E5").Value = Range("E5").Value + 1 Range("A20:E39").ClearContents ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub Here is the code if you have protected the sheet with a password of Tomato. Note that the password appears in this code twice. Change the password to the real password in two places. Sub NextInvoice() ActiveSheet.Unprotect Password:="Tomato" Range("E5").Value = Range("E5").Value + 1 Range("A20:E39").ClearContents ActiveSheet.Protect Password:="Tomato", DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub

FAQ #7

I don't want to have the Save button in the Saved workbook

This is tricky because the name of your shape will change when the worksheet moves to a new workbook. Very carefully follow these steps:

  1. Open the invoice macro workbook
  2. Right-click on the sheet tab that contains your invoice. Choose Move or Copy.
  3. In the To Book: dropdown, choose New Book. Choose the checkbox for Create a Copy. Click OK. This step 3 simulates the ActiveSheet.Copy in the VBA.
  4. Now that you have the invoice worksheet in a new workbook, Ctrl-click on the shape that you want to delete. Ctrl+Click will select the shape without running the macro.
  5. With the shape selected, look to the left of the Formula Bar. The Name Box will show a name such as "Rounded Rectangle 1". Very carefully build a new line of code to delete this shape as shown below just after ActiveSheet.Copy:
Sub SaveInvoiceWithNewName() Dim NewFN As Variant ' Copy Invoice to a New Workbook ActiveSheet.Copy ActiveSheet.Shapes("Rounded Rectangle 1").Delete NewFN = "C:aaaInv" & Range("E5").Value & ".xlsx" ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook ActiveWorkbook.Close NextInvoice End Sub

Troubleshooting & Error Messages

  1. The following features cannot be saved in macro-free workbooks: VB Project. Answer: Your file is currently saved as an XLSX file. I am equally annoyed that Microsoft chose to use a broken file type as the default. Use File, SaveAs and change the file type to either XLSB or XLSM.
  2. Type Mismatch. Answer: The code expects your invoice number to be a number. If you have SS15001 as an invoice number, you will have to figure out how to adapt your code. See FAQ #4 above.
  3. Compile error Expected line number or label or statement or end of statement on NewFN = “F:RobinusinessPCreceiptsInv” & Range(“H10”).Value & “.xlsx”. Answer: VBA does not like slanted quotation marks (also called Typographers quotes). Type the quotation mark in VBA and you will get "F:RobinusinessPCreceiptsInv" & Range("H10").Value & ".xlsx"
  4. We couldn't find C:UserJelenDocuments" Answer: The file path has to be exact. Are you sure you didn't mean C:UsersJelenDocuments? (Note the "s" at the end of users)
  5. Napaka med izvajanjem 1004. Dokument ni shranjen. Odgovor: Pot do datoteke mora biti natančna. Pred shranjevanjem datoteke dodajte novo vrstico z MsgBox NewFN. Zaženite kodo. Pojavilo se bo okno, ki prikazuje pot do datoteke in ime datoteke. Prepričajte se, da med potjo in imenom datoteke obstaja ločilo poti.
  6. Napaka med izvajanjem '1004'. Metoda 'SaveAs' predmeta '_Workbook' ni uspela. Odgovor: FileFormat mora biti FileFormat: = xlOpenXMLWorkbook. Ko ga preberete, se sliši kot »Delovni zvezek Excel Open XML«. To ni Ex One Open XML Workbook. Da, zmedeno je, da sta si številka 1 in mala črka L v videu podobna. 1l. Spremenite svojo X1OPENXMLWORKBOOK v XLOPENXMLWORKBOOK.

Zanimive Članki...