Morning everyone
First time on here,
Im very new to VBA so bare with me, what I am trying to achieve is have excel export my current PO sheet to a PDF once its been opened/calculated. I have the VBA to export to PDF in my module working as per the below.
Sub CreatePdf()
Dim ID As String
ID = Range ("E4).Text
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:="C:\Apps\" + ID + ".pdf", _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True
End Sub
The problem I have is my excel sheet is populated from an export from another application, sometimes it takes 10 seconds and sometimes it takes 1 minute for all the cells to populate. What I would like is for my CreatePdf module to only run once all cells have been populated.
I have tried
Workbook_Activated
Workbook_Open
The Excel sheet won't populate until you actually see it on the screen, ive tried adding wait timers but that hasn't worked either.
The last thing I was going to try was to perhaps have a complete duplicate of my PO sheet. The export would be mapped to say sheet1 and then I would have a duplicate of my PO form on sheet2 which just uses formulas to calculate all the cells i.e cell A1 on sheet1 = cell A1 on sheet2. I would then use a Worksheet_Calculate event to look at all the cells to make sure they match and once they do run the CreatePdf module.
Feels a bit dirty but not done anything like this before. I hope this kinda makes sense.
Id be really grateful for any advice.
Thanks all
First time on here,
Im very new to VBA so bare with me, what I am trying to achieve is have excel export my current PO sheet to a PDF once its been opened/calculated. I have the VBA to export to PDF in my module working as per the below.
Sub CreatePdf()
Dim ID As String
ID = Range ("E4).Text
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:="C:\Apps\" + ID + ".pdf", _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True
End Sub
The problem I have is my excel sheet is populated from an export from another application, sometimes it takes 10 seconds and sometimes it takes 1 minute for all the cells to populate. What I would like is for my CreatePdf module to only run once all cells have been populated.
I have tried
Workbook_Activated
Workbook_Open
The Excel sheet won't populate until you actually see it on the screen, ive tried adding wait timers but that hasn't worked either.
The last thing I was going to try was to perhaps have a complete duplicate of my PO sheet. The export would be mapped to say sheet1 and then I would have a duplicate of my PO form on sheet2 which just uses formulas to calculate all the cells i.e cell A1 on sheet1 = cell A1 on sheet2. I would then use a Worksheet_Calculate event to look at all the cells to make sure they match and once they do run the CreatePdf module.
Feels a bit dirty but not done anything like this before. I hope this kinda makes sense.
Id be really grateful for any advice.
Thanks all
