The following code stops at the red line with "Method 'SaveAs' of object '_Workbook' failed.
FF="xlCSV". It works fine if I replace
Code:
FileFormat:=FF
with
Code:
FileFormat:=xlCSV
Code:
Sub SaveIt(FileNm, FF)
Dim FSO As Object, a
Set FSO = CreateObject("Scripting.FileSystemObject")
If FSO.FileExists(FileNm) Then FSO.DeleteFile FileNm
If FSO.FileExists(FileNm) Then
[code]....
I am still a novice in you standards, but have run into a problem and hoping you all will have some insight for me.
I have a workbook that I am parsing data from the first sheet and putting the data onto the second sheet. Before I place the values in I want to format the cells with boarders, colors, alignment, and in some cases validation (but I can worry about the validation later).
Currently what I am doing is using a 3rd page that has a group of cells that have the formats I want in place before I start. The code will then parse the data and when it finds the data I need go to sheet 3 and grab a range of cells and paste those cells to sheet 2 and then write the data over those cells. This allows me to format the document as I create it.
The problem is that I am going through 25k of lines and on average this function is running 40+ minutes. If I run the same script with out the formatting it only takes at most 2 minutes.
So on to the question.
Is there a way that I can start of my script by grabbing all the format information before I parse the data and save it as a variable? Then instead of doing a copy of the format it would just apply the formatting that is saved already. I would think this would be much faster of a process.
I am still learning VBA and am wanting to know can you save a file with a variable week number?
e.g file name = 657 13082012 CP
The Following week would need to be 658 20082012 CP
I am trying to save a file into a CSV format. When I choose that save as option the file is saved with the commas but a need each field to be surrounded by quotes.
Is there an easy way to do this? I know I could append the " around the data in each cell with a formula, but I'm hoping there is a cleaner way to do it.
I would also like to set the extention of the saved file to .txt when I save it, but Excel 2000 does not seem to let me do that.
I've wriiten the following routine :
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
' Unhides all columns prior to saving
ActiveSheet. Unprotect password:="test"
Cells.Select
Selection.EntireColumn.Hidden = False
Range("A1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, password:="test"
Response = MsgBox("Have you considered all the items on the checklist?", vbYesNo)
If Response = vbYes Then
Go_To_Checkbox
I want the routine to stop without saving (after running Go_To_Checkbox) if the answer to "Have you ticked the checklist checkbox?" is 'No'. I know the 'Stop' function will do it but I just want to exit the routine without saving, rather than the routine stopping and calling up the Visual Basic editor.
I have 2 subs routines in a mod. I have declared the two variables at the top of the sub. However when I call the second sub the variable's are not passed along.
Here is an example script. All in one mod. I have taken out the junk in between to help edit the problem.
I'm looking to pass the same bnumber and dnumber to the secound sub.
Here's an array I have that is filled with values that come from a worksheet. I'd like to have it so that once this array is full the code that fills it up (see below) gets skipped on subsequent runs of the subroutine. What's a good way to do this? Seems like when I Dim the Array it gets blanked.
Code:
'Fill Bulb Array with House number (j) and Bulb Wattages
j = 1
Do While j
What I am looking to do is create a Dos.bat file to be run in the middle of the night that opens Excel. From there I will need Excel to open a .txt file from a specific folder, format the file, then re-save as an .xls file.
Each of the .txt files in the specific folder is named like the following:
BCKLOG_062807
BCKLOG_062707
and a new file is being generated to the folder each night (with the new date in the filename).
Is there a way I can use the macro to loop through only the new files being placed in the folder, since the old files will already have their .xls counterpart saved ???
If this is not a clear enough description, please let me know and I will attempt to explain better.
what format is used when you save an excel file as "unicode." I am using excel 2002 on XP.
I have been asked to provide a UTF-8 formatted unicode file for use by another program but I am not sure which formatting excel uses.
I am looking for VBA code that will save Excel 2010 files in semi-colon delimited format without having to chage the universal language options.
View 1 Replies View RelatedI am in the process of re-building a major project due to bugs. In the process, I plan to re-organize my sub-routine grouping within modules. In the source file, I have 18 standard modules, with 10-30 routines in each one.
What I would like is a printout of each routine name and which module it resides in, so I can check off each one as I copy it and paste it into the new file. I see that I can get a psuedo tree-view of my project in th eObject Browser, but can't figure out how to print that.
I am trying to develop a routine that will save the file I am currently working on, then save the current tab as a *.csv file with the name equal to cell "C1" in the same directory as the original file. Then I would like it to close the .csv file and reopen the original file all while being generic with the naming so that if the file name and location changes, the script will still work. I recorded a macro to get me started.
View 9 Replies View RelatedI have written a VBA procedure which loops through excel files in an array: in turn it opens the file, refreshes the data, saves the file and closes. I would like to write an error handler which stores the file which errored (copied from the loop iteration) and the error description to an array. The error handler should then close the file and continue with the next iteration - e.g.
Code:
If ActiveWorkbook.ReadOnly Then
ActiveWorkbook.Close
Next i
*Although a generic if error may be better.
My idea is that all files which errored in some way will be stored in an array which I can automatically print/send to a user so they can manually figure out the problem. (This element falls outside the range of this query).
Below is a simple example of my efforts which only contains one file in the array to make things simpler: the error handler section won't work, it's just there to show my thinking
Code:
Sub Refresh_CRIS()
On Error GoTo Errorhandler
Dim routepath As String
routepath = "
chdfsSharedAreaPrivateTest"
ChDir routepath
[Code] ........
Errorhandler:
On Error Resume Next
Failed = Array(i)
If ActiveWorkbook.ReadOnly Then 'Just an example for testing
ActiveWorkbook.Close
Else
MsgBox "Pivots which failed to refresh:" & Failed '& ", ", 0, "Debug" 'Test with msgbox
End If
End Sub
Using Excel 2010
I have figured out how to save the workbook using a variable to saveas but I would like to create a new folder to place the new workbook into. I have tried a few things a am not having any luck. I recorded a macro and the code it produced is as follows.
View 6 Replies View RelatedI have looked at many different examples of uses of the average function but I haven't found any examples of what I need it to do. Here is the code I am trying to use, but I am getting some errors.
View 6 Replies View RelatedI have an existing application that uses DAO sql queries to populate a form. It has always worked fine. Earlier this week, I password protected the spreadsheet. Now my DAO query fails. I get a run-time error '3161'. Could not decrypt file.
' Const stExtens As String = "Excel 8.0;HDR=Yes;"
Const stExtens As String = "Excel 8.0;HDR=Yes;PWD=money16;"
Private Sub Execute_Query(SQL)
'Instantiate the DAO objects.
strDb = ThisWorkbook.FullName
Set DAO_ws = DBEngine.Workspaces(0)
' code fails on the line below
Set DAO_db = DAO_ws.OpenDatabase(strDb, False, True, stExtens)
Set DAO_rs = DAO_db.OpenRecordset(SQL, dbOpenForwardOnly)
End Sub
I have a worksheet that uses a web query to return data, and display it in a worksheet. This works fine. I now want to use VBA to call a URL, but not display the information returned in a worksheet. Rather, I want to store that information in a variable, and then use it in my VBA sub.
View 4 Replies View RelatedWhen the data is refreshed, how can i keep the previous data and use it in a graph that automatically updates?
View 12 Replies View RelatedVBA coding for automatically saving an excel file as another file using the current date as part of the file name together with "32ga" as a constant add-in. I also what this macro to run at a particular time of the day let say 00:20hrs. The excel file i want to save as is always open . It has data that changes every 24-hrs.
View 1 Replies View Relatedam using Excel 2010 and having issues trying to save a worksheet to a specified file location with the save date....
I have tried several posts form this forum and elsewhere and can't seem to get the macro to do what I want.....
I want to save a 'worksheet' from an open workbook that I use for updating information to the same file path as the workbook with the date the file saved...
I want to be able to disable the save query definition option for a worksheet using VBA. Right now I have to right click a cell and then go to Data Range Properties and then uncheck the save query definition box. If there is a way to automate this that would be great.
View 3 Replies View RelatedI have a Sheet ( Named "Summary" for Example ) of about 4,000 Rows that has a LOT of Conditional Formatting.
I Added Another 100 Rows this Morning and when I Tried Saving it a Message Saying that Not All the Formatting for the New Data that had Been Added had Been Saved. Is there a Macro or Something I can Run that will Make the Conditionally Formatted Cells Stay the Colour that they are but Delete the Conditional Formatting Part of it Achieved Using "Format" & "Conditional Format" from the Menu Please.
Ideally I would like to be Able to Enter the Number of Rows ( From Row ? to Row ? ) that I want this to Apply to.
In order to get inventory data from our Incode database, I've been creating an output file with the extension .IRP Thinking that excel would not bring in this data from the .IRP file directly, I would then create a txt file of it with notepad. I've recently discovered that I can bring it in directly from the .IRP
The file being created is in this format:
IN Stock Status Report - 6385.IRP
The number portion, 6385 in the name will vary everytime I generate a new report. However the text portion will not.
Currently, I've recorded a macro the bring in the data to my excel spreadsheet.
But it will only bring in data for the set filename.
What I am trying to do is that I have an excel file with macros and it is a read-only file. In order for the user to save, I want them to only be able to save as a .xlsx file as it disables all macros. If for whatever reason, the user wants to save the file as another .xlsm file, they should be allowed but before they save, a "are you sure you want to save as .xlsm?" message should pop up.
All the options in the save as box should still be available in case they want to save in that particular format. Just that the .xlsx should be the default.
In order to export an excel table into another program I first need to save the excel file as .txt. The .txt file can then be imported by the other program.
First I however need to make excel understand that the value should be a text and not a value. I therefore format the number as text (0000150235) by adding "0000150235". After saving the file as .txt the format changes from "0000150235" to """0000150235""". I do however need the format in the .txt file to be "0000150235". Can anyone tell me how I can save "0000150235" as .txt and get the value "0000150235" in the .txt file.
I am trying to count the occurences of combinations within a range defined by contiguous cell values in one column. My problem lies with setting the value of variables that are queried within the defined range. My macro should;
1)Set ComboValue1 & ComboValue2 values to A1 & B1 cells values respectively
2)define range to be searched by how many contiguous values there are in column D
3)search column E for 2 figure combination (defined by A1 & B1). If present increment counter by 1 and add to column C (to be visible in worksheet). If no match carry on
4)define new range by next set of contiguous values in column C
5) step 3
6)when all possible contiguous ranges defined and searched from column c, perform again on next combination down columns A & B with refreshed occurence counter
7)finish when all combinations are searched for
Sub CountComboOccurence()
Dim ListCounter As String
Dim ProgCounter As String
Dim Counter As Integer
Dim ComboValue1 As String
Dim ComboValue2 As String
Dim Rng As Range
Application. ScreenUpdating = False
ListCounter = 0
ProgCounter = 0
Counter = 0
ComboValue1 = 0.......................
I have a macro that copy one sheet of the Active workbook and sends it via email.
I need to add a code in this sheet so when one opens it from the email, with a command button to be able to save the file to specific, fixed folder on the local network with it’s original file name.
When I enter a number, say 2456 in this textbox why does it display "$2.45" instead of "$2,456". I've tried fiddling with the ###'s and .'s but it just gets worse.
[Code] ....
I use web queries to download stock options from MSN Money.
If I manually refresh the queries (right click -refresh) the downloaded
prices are in number format.
If I use:
I am wanting to use Excel to perform a web query into an online database our company uses. One of the columns I am wanting to import contains numbers that are 20 digits long & they begin with 4 zero's. The problem is that when Excel imports the query, if I fprmat the cells & preserve the formatting, it cuts off the first 4 zero's & replaces the last number with a zero. Basically, it looks as if it is only importing 15 numbers. Is there a way around this ; is there a way to force Excel to import all 20 numbers? If I don't format the cells, it imports in the following format - 4.20017E+15
View 4 Replies View Related