-
-
Sub WorkBook_Open()
-
RemoveEmptyRow
-
ConcatenateColumn
-
DeleteBlankColumns
-
SaveFile
-
End Sub
-
-
Sub SaveFile()
-
'ActiveWorkbook.Save
-
ThisWorkbook.Saved = True
-
Application.Quit
-
Application.ActiveWindow.Close SaveChanges:=True
-
ActiveWorkbook.Close SaveChanges:=True
-
'ActiveWorkbook.SaveCopyAs ("D:\save\" & Format(Now, "ddmmyy") & ".xls")
-
'ActiveWorkbook.SaveAs filename:="D:\RESULT\" & Format(Now, "ddmmyy") & ".xls", FileFormat:=xlCurrentPlatformText, CreateBackup:=False
-
'ActiveWorkbook.SaveAs Filename:="D:\RESULT\Text.txt", FileFormat:=xlCurrentPlatformText, CreateBackup:=False
-
End Sub
-
-
Sub RemoveEmptyRow()
-
Dim i As Long
-
Dim DelRange As Range
-
On Error GoTo Whoa
-
Application.ScreenUpdating = False
-
'Path = ThisWorkbook.Path
-
'Path = ActiveWorkbook.Path
-
For i = 1 To 1500
-
If Application.WorksheetFunction.CountA(Range("A" & i & ":" & "DG" & i)) = 0 Then
-
If DelRange Is Nothing Then
-
Set DelRange = Rows(i)
-
Else
-
Set DelRange = Union(DelRange, Rows(i))
-
End If
-
End If
-
Next i
-
If Not DelRange Is Nothing Then DelRange.Delete Shift:=xlUp
-
LetsContinue:
-
Application.ScreenUpdating = True
-
Exit Sub
-
Whoa:
-
MsgBox Err.Description
-
Resume LetsContinue
-
ActiveWorkbook.Save
-
End Sub
-
-
Sub ConcatenateColumn()
-
'Path = ActiveWorkbook.Path
-
'For i = A To Cells(Rows.Count, "BE").End(xlUp).Row
-
For i = 1 To Cells(Rows.Count, "BE").End(xlUp).Row
-
Cells(i, "BE").Value = Cells(i, "BE").Value & Cells(i, "BF").Value
-
Next i
-
For i = 1 To Cells(Rows.Count, "AE").End(xlUp).Row
-
Cells(i, "AE").Value = Cells(i, "AE").Value & Cells(i, "AF").Value & Cells(i, "AG").Value
-
Next i
-
For i = 1 To Cells(Rows.Count, "G").End(xlUp).Row
-
Cells(i, "G").Value = Cells(i, "G").Value & Cells(i, "H").Value
-
Next i
-
For i = 1 To Cells(Rows.Count, "K").End(xlUp).Row
-
Cells(i, "K").Value = Cells(i, "K").Value & Cells(i, "L").Value
-
Next i
-
For i = 1 To Cells(Rows.Count, "M").End(xlUp).Row
-
Cells(i, "M").Value = Cells(i, "M").Value & Cells(i, "N").Value
-
Next i
-
For i = 1 To Cells(Rows.Count, "AI").End(xlUp).Row
-
Cells(i, "AI").Value = Cells(i, "AI").Value & Cells(i, "AJ").Value & Cells(i, "AK") & Cells(i, "AL")
-
Next i
-
For i = 1 To Cells(Rows.Count, "AM").End(xlUp).Row
-
Cells(i, "AM").Value = Cells(i, "AM").Value & Cells(i, "AN").Value & Cells(i, "AO").Value
-
Next i
-
For i = 1 To Cells(Rows.Count, "AP").End(xlUp).Row
-
Cells(i, "AP").Value = Cells(i, "AP").Value & Cells(i, "AQ").Value & Cells(i, "AR").Value
-
Next i
-
For i = 1 To Cells(Rows.Count, "AS").End(xlUp).Row
-
Cells(i, "AS").Value = Cells(i, "AS").Value & Cells(i, "AT").Value
-
Next i
-
For i = 1 To Cells(Rows.Count, "AV").End(xlUp).Row
-
Cells(i, "AV").Value = Cells(i, "AV").Value & Cells(i, "AW").Value
-
Next i
-
For i = 1 To Cells(Rows.Count, "BA").End(xlUp).Row
-
Cells(i, "BA").Value = Cells(i, "BA").Value & Cells(i, "BB").Value & Cells(i, "BC").Value
-
Next i
-
For i = 1 To Cells(Rows.Count, "BL").End(xlUp).Row
-
Cells(i, "BL").Value = Cells(i, "BL").Value & Cells(i, "BM").Value
-
Next i
-
For i = 1 To Cells(Rows.Count, "BP").End(xlUp).Row
-
Cells(i, "BP").Value = Cells(i, "BP").Value & Cells(i, "BQ").Value
-
Next i
-
For i = 1 To Cells(Rows.Count, "BS").End(xlUp).Row
-
Cells(i, "BS").Value = Cells(i, "BS").Value & Cells(i, "BT").Value
-
Next i
-
For i = 1 To Cells(Rows.Count, "BS").End(xlUp).Row
-
Cells(i, "BS").Value = Cells(i, "BS").Value & Cells(i, "BT").Value
-
Next i
-
For i = 1 To Cells(Rows.Count, "BW").End(xlUp).Row
-
Cells(i, "BW").Value = Cells(i, "BW").Value & Cells(i, "BX").Value
-
Next i
-
For i = 1 To Cells(Rows.Count, "CA").End(xlUp).Row
-
Cells(i, "CA").Value = Cells(i, "CA").Value & Cells(i, "CB").Value
-
Next i
-
For i = 1 To Cells(Rows.Count, "CH").End(xlUp).Row
-
Cells(i, "CH").Value = Cells(i, "CH").Value & Cells(i, "CI").Value & Cells(i, "CJ").Value
-
Next i
-
For i = 1 To Cells(Rows.Count, "CL").End(xlUp).Row
-
Cells(i, "CL").Value = Cells(i, "CL").Value & Cells(i, "CM").Value
-
Next i
-
For i = 1 To Cells(Rows.Count, "CO").End(xlUp).Row
-
Cells(i, "CO").Value = Cells(i, "CO").Value & Cells(i, "CP").Value
-
Next i
-
For i = 1 To Cells(Rows.Count, "CR").End(xlUp).Row
-
Cells(i, "CR").Value = Cells(i, "CR").Value & Cells(i, "CS").Value
-
Next i
-
For i = 1 To Cells(Rows.Count, "CT").End(xlUp).Row
-
Cells(i, "CT").Value = Cells(i, "CT").Value & Cells(i, "CU").Value
-
Next i
-
For i = 1 To Cells(Rows.Count, "CV").End(xlUp).Row
-
Next i
-
End Sub
-
-
Sub DeleteBlankColumns()
-
'Path = ActiveWorkbook.Path
-
'Declaring the variable lColumn as long to store the last Column number
-
Dim lColumn As Long
-
'Declaring the variable iCntr as long to use in the For loop
-
Dim iCntr As Long
-
'Assigning the last Column value to the variable lColumn
-
lColumn = 111
-
iCntr = 1
-
'Using for loop
-
'We are checking the each cell value if it cell is 0 (equals to zero value)
-
'And deleting the Column if true
-
For iCntr = lColumn To 1 Step -1
-
If Cells(1, iCntr) = 0 Then
-
Columns(iCntr).Delete
-
End If
-
Next
-
ActiveWindow.Visible = True
-
End Sub
-
-
I Want to run all script in workbook_open() which will run the macro automatically after open. But, once I open the excel file using bat file, this error coming out.
"Method 'Range of object'_Global'failed"
Please help me
3 7825
This thread may more properly belong in MS Excel.....
You have not told us at which line you are receiving this error. I don't see any "_Global" anywhere in your code, which makes me think it the error may be firing from somewhere else. But, I wouldn't know where to start without some more information.
The error shown at line 48. I have use personal macro vba. Personal macro script available every-time we open the excel no matter which file. In my case, I have use batch file to run the excel file which contain data. After that, I use -
Sub Workbook_Open()
-
-
end sub
-
to auto run the script. I have call Sub SaveFile() , Sub RemoveEmptyRow(), Sub ConcatenateColumn(), and Sub DeleteBlankColumns() in Sub Workbook_Open() in order to run the script inside. I use this script to format the file because of empty rows, got some extra column which takes the data from previous column, and got blank column.
After I open the excel using batch file, the error is shown. Then. I click ok and go to developer, click macro and manually run the Workbook_open() on that data file.
Thank you for your response. Sorry for unclear question. Ask anything that you want to know to help me. I will answer. Please help.
Has this code always worked but now has come up with errors? Or, is this new code that you are trying to get to run properly?
Line 48 is a comment line, and has no meaning for the compiler, so it will not cause the error.
One possibility for your error may have to do with how you are referring to your cells. The Cells() property requires numeric values, not text. So
should probably be
etc.
I've made similar mistakes in the past, as Excel referencing is a bit different than Excel VBA referencing.
Hope this hepps!
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Todd |
last post by:
This has really been my day for wierd problems sorting one of my forms (a
subform actually)... so here's another one.
I am now receiving the following error
Run-time error '-2146500594...
|
by: Jeffrey Bradshaw |
last post by:
Is there any way to make a copy of a word.range object? I have a range in a
document and I need to monkey around with it but I don't want to change the
original in the document. I've tried...
|
by: Lauren Wilson |
last post by:
Hi folks,
I have an Access 2003 app that works perfectly on my computer.
However, when we install it on OTHER computers that ALSO have access
2003 we get an error on startup:
Error:...
|
by: windandwaves |
last post by:
Hi gurus
I have a class from which I create an object. I want this object to be
available in all my functions for my application. Basically, I want to
make the object global. What is the best...
|
by: ielamrani |
last post by:
Hi,
I am getting this error when I try to export to an excel sheet. When I click on a button to export the first time it's fine, I rename the exported excel sheet and I try to export it again and I...
|
by: kaushalgajjar |
last post by:
Hi,
I had created an active-x control (ocx) in vb6 and i have used in VB.Net and HTML using ClassId reference and the active-x was working fine with ASP.Net, HTML and VB6.
Now the active-x is...
|
by: CoreyReynolds |
last post by:
Hey all,
I have a piece of code that dumps a bunch of data into a spreadsheet. Also rearranges it into a pivot table and then graphs the pivot table as well so my boss can get a clear view of the...
|
by: fnemo |
last post by:
I'm getting the error - Method 'Item' of object 'Forms' failed . Earlier this error was not occuring.
In the below code, first textboxes are created dynamically in the form "display_result"....
|
by: Hema Suresh |
last post by:
Hi all
I created a database via VB and saved it in excel sheet
I have 10 command buttons and 10 text box controls on the vb form and i coded in the way to get the data from the user
once the...
|
by: ChiomaJennifer |
last post by:
hi...
i have an access application in which i need to open an existing excel sheet, find a date (already in the sheet) and populate a row (with the date cell column) with either 1 or 0
this means...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |