VBA Excel SaveAs works on one PC but corrupts file on another
I have written a MACRO that does what I need it to do and the end result is saving a file as
finished file and then open that file and run another MACRO.
This script works exactly as I need it to and there are no issues until I try to run this code on another computer.
Here is where the code breaks.
' Note I have tried several file formats. ocd_wb.SaveAs Filename:="finished file", FileFormat:=xlOpenXMLWorkbook Set re_wb = Workbooks.Open("finished file")
When I try the above on my Coworkers PC it errors out and when I check the file it is corrupt.
I am not sure what could cause this. Everything I check on the net say to save as various file type.
(52, xlOpenXMLWorkbook, xlOpenXMLWorkbookMacroEnabled). None of which work. Note that all of those formats work on my PC just fine so I am not sure it is an issue with the file type.
Update: We both have Office 2010 so its not a version issue.
See also questions close to this topic
VBA: why I cannot use "=" to transfer data from one sheet to another sheet?
I want to use
"="operator to transfer data from "Sheet1" to "Sheet2" because it is fast. However, the following code doesn't work:
Sheets("Sheet2").range(cells(1, 1),cells(1,5)).value = Sheets("Sheet1") .range(cells(1, 1),cells(1,5)).value
Delete hidden rows in auto-filter in coauthoring spreadsheet
What I want to do is to reset the filter then delete all the rows below row 9.
This should be a easy task, but this is a coauthoring spreadsheet, i want to avoid using
ActiveSheet.ShowAllDataas this will require all people to refresh the spreadsheet.
I tried to use keybd_event to simulate ALT+A+C to clear the filter, but it dosen't quite work.
keybd_event VK_Alt, 0, 0, 0 keybd_event VK_A, 0, 0, 0 keybd_event VK_A, 0, KEYEVENTF_KEYUP, 0 keybd_event VK_Alt, 0, KEYEVENTF_KEYUP, 0 keybd_event VK_C, 0, 0, 0 keybd_event VK_C, 0, KEYEVENTF_KEYUP, 0 Worksheets(1).Rows(9 & ":" & Worksheets(1).Rows.Count).Delete
My thought is when the marco simulate alt+a+c, the hidden rows do not show until the whole macro is finished. So it only delete those visible rows, and after the macro finished, those hidden rows come out.
Dose anyone know better ways to do it? Any help would be appreciated! Thanks!
How to play windows media player in VBA, starting from a spesific duration
I am doing a project of video based analysis. I want to know how to play a windows media player in excel using vba, for a specific time, such as playing the video from duration 00.06 until 00.14. Because the current state that I have known is playing the video using vba code from 00.00 to the end of the duration.
Thank you, your help will be much appreciated
VBA Highlight Cells that don't match master list in a column
I'm having some trouble taking apart this code I found. I'm looking to error check a group of cells and I'd like to highlight the ones that don't match a Master list.
For some reason I can't get this to highlight the cells that DON'T match the list.
Please help me through this, I'd be very grateful!
Dim myRange, v, f, a Set myRange = Range("P12:P200") For Each v In Workbooks("Formula_Weighup Audit Auto-Fill Final").Worksheets("Active Master List").Range("J2:J1054") Set f = myRange.Find(what:=v, lookat:=xlPart) If f Is Nothing Then a = f.Address Do f.Interior.ColorIndex = 5 Set f = myRange.FindNext Loop While f.Address <> a End If Next MsgBox "Error: R&D #(s) do not exist" & vbNewLine & "(see highlighted items)" Exit Sub
Runtime error 1004 range assigned function
I am doing operations on stock data, and I am attempting to assign functions to ranges.
Sub RSIFunction() Dim numberOrRowsToSkip As Integer: numberOFRowsToSkip = 70 Dim TrialsToExecute As Integer: TrialsToExecute = 127 Dim firstFormula As Range: Set firstFormula = Range("J154:J220") Dim secondFormula As Range: Set secondFormula = Range("K154:K220") Dim thirdFormula As Range: Set thirdFormula = Range("L154:L220") Dim fourthFormula As Range: Set fourthFormula = Range("M167:M220") Dim fifthFormula As Range: Set fifthFormula = Range("N167:N220") Dim sixFormula As Range: Set sixFormula = Range("O167:O220") Dim sevenFormula As Range: Set sevenFormula = Range("P167:P220") Dim i As Integer For i = 0 To TrialsToExecute firstFormula.Offset(i * numberOFRowsToSkip).FormulaR1C1 = _ "=RC[-6]-R[-1]C[-6]" secondFormula.Offset(i * numberOFRowsToSkip).FormulaR1C1 = _ "=IF(RC[-1]>0,RC[-1],"")" thirdFormula.Offset(i * numberOFRowsToSkip).FormulaR1C1 = _ "=IF(RC[-2]<0,RC[-2],"")" fourthFormula.Offset(i * numberOFRowsToSkip).FormulaR1C1 = _ "=(SUM(R[-13]C[-2]:RC[-2])/14)" fifthFormula.Offset(i * numberOFRowsToSkip).FormulaR1C1 = _ "=(SUM(R[-13]C[-2]:RC[-2])/14)" sixFormula.Offset(i * numberOFRowsToSkip).FormulaR1C1 = _ "=RC[-2]/RC[-1]" sevenFormula.Offset(i * numberOFRowsToSkip).FormulaR1C1 = _ "=IF(RC[-2]=0,100,100-(100/(1+RC[-1])))" Next i End Sub
Runtime error '1004': Application-defined or Object-defined error
After pressing Debug, this line is highlighted:
secondFormula.Offset(i * numberOFRowsToSkip).FormulaR1C1 = _ "=IF(RC[-1]>0,RC[-1],"")
Replace countifs with advanced filter or other macro - guidance appreciated
I have quite a heavy workbook. I am curious if any of you could advise how to best do it so that the processing speed isn't affected much.
TblOccupancy[code] TblOccupancy[start] TblOccupancy[end] TblOccupancy[setting]
The above is used on another sheet.
column B6:B370contains dates
c3:ie5contains 3 criteria
I have created a formula that counts number of users of given code present on specific date.
I was hoping you could help me converting this into macro [Excel-vba]
and that it would in turn speed up processing and automation in general.
This is what I use in
Thank you in advance. At the moment testing advanced filter, struggling with multi-criteria & between dates. I will comment if i solved this one. In the meantime sending hugs for anyone willing to help!
read only excel file saving without read-only propertie?
I have an excel template which I have made read only (in saves as > tools > general options: password to open) to prevent it from being modified by other users. When executing a script, the file is being saved at a set location with a set name. I want this script to remove the 'password to open' setting in the original file.
I tried to modify my 'save as' script, but that keeps the 'password to open' setting as it was set in the original file.
How can I get rid of that 'password to open'-setting using my vba script?
the save as script as it works now is:
Sub refreshpivots() Dim workbook_Name As Variant Dim location As String Dim workbookdirectory As String ActiveWorkbook.RefreshAll workbookdirectory = "M:\Commercie\Marktdata\IRi\Segment Ontwikkeling\" workbook_Name = Application.GetSaveAsFilename(fileFilter:="Excel binary sheet (*.xlsb), *.xlsb", InitialFileName:=workbookdirectory & ActiveWorkbook.Name) If workbook_Name <> False Then ActiveWorkbook.SaveAs filename:=workbook_Name, Password:="", FileFormat:=50 End If End Sub
Save troublesome webpage and import back into Python
I am trying to extract some information from a variety of pages and struggling a bit. This shows my challenge:
import requests from lxml import html url = "https://www.soccer24.com/match/C4RB2hO0/#match-summary" response = requests.get(url) print(response.content)
If you copy the output into Notepad, you cannot find the value "9.20" anywhere in the output (the Team A odds in the bottom right of the webpage). However, if you open the webpage, do a Save-As and then import it back into Python like this, you can locate and extract the 9.20 value:
with open(r'HUL 1-7 TOT _ Hull - Tottenham _ Match Summary.html', "r") as f: page = f.read() tree = html.fromstring(page) output = tree.xpath('//*[@id="default-odds"]/tbody/tr/td/span/span/span/text()') #the xpath for the TeamA odds or the 9.20 value output # ['9.20']
Not sure why this work-around works but that is above me. So what I would like to do is save a webpage to my local drive and open it in Python, as above and carry on from there. But how do I replicate the Save-As in Python? This does not work:
import urllib.request response = urllib.request.urlopen(url) webContent = response.read().decode('utf-8') f = open('HUL 1-7 TOT _ Hull - Tottenham _ Match Summary.html', 'w') f.write(webContent) f.flush() f.close()
It gives me a webpage but it is a fraction of the original page...?
Allow user to choose the save file path for "print PDF"
I have a commandbutton on the Excel file that allows the user to automate the "Print PDF" feature with a click. The issue being, at the moment I can only save the file to the specified location within the code. I want to let the user choose their save directory similar to the actual saving feature.
Here is the code I have so far:
Private Sub PrintPDF_Button_Click() Dim mySheets As Variant, sh mySheets = Array("COVER", "SCOPE", "SUMMARY", "Updated Hours EST", "RATES") Sheets(mySheets).Select ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Users\sravi\test" & _ ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, _ IgnorePrintAreas:=False, openafterpublish:=True End Sub