PDA

View Full Version : Solved: Ending Macro



eahowell
06-01-2011, 08:44 AM
Background Information:
I have written a set of code that upon opening the document it prompts the user to open and insert data from a .txt file of data and separate the data into the appropriate cells then prompt the user to SaveAs.

Then intention of this is to take a large quantity of data and bring it into Excel to then analyze it or view it later.

Problem/Next Step:
Now the issue I have is when you go to open one of the saved documents of data after it's been separated it runs the macro again.

Is there a command to to kill Workbook_Open event?

I was trying an if statement to check if the name of the file name is the original or one of the saved but I am not sure the correct syntax since I do not have much VBA experience.

I put my code below but I know this isn't right since it won't compile.
Code:


Private Sub Workbook_Open()
Dim WBName As String
ActiveWorkbook.Name = WBName
If WBName = "DataExporter.xls" Then
'Everything below here within the IF statement runs correctly
Dim cell_1, cell_2, rng_1 As Range
Sheet1.Cells.Clear
Set cell_1 = Cells(1, 1)
Set cell_2 = Cells(1, 1)
Set rng_1 = Range(cell_1, cell_2)
rng_1.Select
Call DoTheImport
Call SeparateData
Else
End If
End Function


Thanks in advance!
Liz

shrivallabha
06-01-2011, 09:01 AM
Welcome to VBAX.
1. What version of Excel are you using?
2. How are you creating the new working copy?
If you are using SaveAs then it will save the workbook and inherit the same VBA Project as parent.

eahowell
06-01-2011, 09:49 AM
I am writing it in Excel 2010.. But saving it as an older version because the computer I will be using it on does not have the newer version.

Yes I am using the SaveAs. It does have the same VBA parent. So I was hoping if I could write an if statement to only run the subs if it had the original document name. Otherwise if it is was one of the other documents after the SaveAs it will do nothing.

shrivallabha
06-01-2011, 10:17 AM
1. In case of 2010 (I don't have it), then I suppose the name will be "DataExporter.xlsm" (macro enabled)

2. Does the import result in creating new sheets everytime? If yes then perhaps then you should use something like:
Sheets(sSheetName).Copy

This will create a new workbook without macros and then save it as:
ActiveWorkbook.SaveAs Filename:=sSaveFileName

3. If all the users have 2007+ then save it as *.xlsx (without macros) which will also solve it.

eahowell
06-01-2011, 11:52 AM
How would I check to see what the name of the opened document is?

Chabu
06-01-2011, 12:13 PM
Your code could create a new workbook and write the data in that one instead of in the workbook you open.

An alternative is that you write some control value in your workbook that you check in the code before starting your import.

shrivallabha
06-02-2011, 07:54 AM
How would I check to see what the name of the opened document is?
If you want to know the document name then use:
ThisWorkbook.Name
Here I am supposing that you have put this in the workbook_open event code posted at #1.