如何使用命令按钮自动保存为单元格值单击Vba Excel？

  • Z时代
  • 2024-01-10
  • 分类：问答

想要添加第二个功能，点击Excel中的命令按钮和VBA代码 - 第一个函数将数据从工作表1（订单）填充到工作表2中的数据库日志。寻找第二个要执行的功能是从订单中自动保存单元格值。谢谢！如何使用命令按钮自动保存为单元格值单击Vba Excel？

Private Sub CommandButton1_Click() 
    Dim OrderDate As String, PONumber As String, Vendor As String, ShipTo  As String, SKU As String 
    Dim R As Long, LastSKURow As Long, NextDBRow As Long, OFrm As Worksheet, DB As Worksheet 
    Set OFrm = Worksheets("Order Form 1") 
    Set DB = Worksheets("Database") 
    OrderDate = OFrm.Range("B3") 
    PONumber = OFrm.Range("D3") 
    Vendor = OFrm.Range("B7") 
    ShipTo = OFrm.Range("D7") 
    LastSKURow = OFrm.Cells(OFrm.Rows.Count, "F").End(xlUp).Row 
    For R = 3 To LastSKURow 
     SKU = OFrm.Range("F" & R).Value 
     NextDBRow = DB.Cells(DB.Rows.Count, "A").End(xlUp).Row + 1 
     DB.Range("A" & NextDBRow).Value = OrderDate 
     DB.Range("B" & NextDBRow).Value = PONumber 
     DB.Range("C" & NextDBRow).Value = Vendor 
     DB.Range("D" & NextDBRow).Value = ShipTo 
     DB.Range("E" & NextDBRow).Value = SKU 
    Next R 
    Application.ScreenUpdating = False 
    Dim Path As String 
    Dim filename As String 
     ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, 
     Path = "C:\PDF\" 
     filename = OFrm.Range("D3") 
     OFrm.SaveAs filename:=Path & filename & ".pdf", FileFormat:=xlPDF 
     OpenAfterPublish:=False 
    Application.ScreenUpdating = True 
End Sub

回答：

通过自动保存您是否意味着它可以保存excel工作簿？如果是的话，你可以添加这样的事情...

Application.ActiveWorkbook.Save

如果这是你正在寻找没有什么，请让我知道...

回答：

对不起它了几天，我一直很忙。

根据你以前的评论，我写的这个子程序应该可以解决你的问题。我选择将文件保存为pdf，假设您已将打印区域设置为表单。我选择了pdf，因为您声明它是一个订单，并且假设表单将在稍后编辑。我选择了它，因为它是一个表单，并且可能有一些计算的字段附加到另一个工作表，如果您分离工作表，这将不起作用。但是，如果您决定只需要一份工作表的副本，我也包含了该代码。你所需要做的就是用注释填写区域，并将这个功能附加到命令按钮:)。如果您想了解有关如何执行此操作的其他信息，请告诉我们。

对于PDF的功能，你可以在这里看到的信息：https://msdn.microsoft.com/en-us/vba/excel-vba/articles/workbook-exportasfixedformat-method-excel

对于另存为信息，您可以在这里看到的信息：https://msdn.microsoft.com/en-us/vba/excel-vba/articles/worksheet-saveas-method-excel

Public Sub savefile() 
Dim wb As Workbook 
Dim ws As Worksheet 
Dim rngfilename As Range 
Dim strSavePath As String 
'assumptions: 
'you are saving the file to a fixed location and the name of the file within the range is continually changing 
'you have set the print areas 
Set wb = Application.ActiveWorkbook 
Set ws = wb.ActiveSheet 
Set rngfilename = ws.Range("A1") 'the range that contains your filename 
strSavePath = "C:/" 'the location that you would like to save your file 
'ensure that there is a value within the filename field declared above 
If rngfilename.Value = "" Or Len(rngfilename) = 0 Then 
    MsgBox "You have not entered a filename" 
    Exit Sub 
End If 
'if you do not have print areas set ignore print areas to true in the function bellow 
ws.ExportAsFixedFormat xlTypePDF, strSavePath & rngfilename.Value, xlQualityStandard 
'for the save as function 
'ws.SaveAs strSavePathe & rngfilename.Value 
End Sub

以上是 如何使用命令按钮自动保存为单元格值单击Vba Excel？ 的全部内容， 来源链接： utcz.com/qa/258621.html

回到顶部