Excel Automation - Blank worksheet/file
Dim ExcelWbk As Excel.Workbook
Dim ExcelWks As Excel.Worksheet
Dim sTemplate As String ' used for Template file path
Dim sTempFile As String ' used for Template file neame
Dim sOutput As String ' used for Output file name
Dim lRecords As Long ' used for count of number of records
Dim iRow As Integer ' Spreadsheet row index
Dim iCol As Integer ' Spreadsheet column index
Dim iFld As Integer ' Record field number index
Const cTabOne As Byte = 1 ' used to select 'Sheet 1'
Const cTabTwo As Byte = 2 ' used to select 'Sheet2'
Const cStartRow As Byte = 7 'initial Row index
Const cStartColumn As Byte = 1 'initial Column index
Thank you for taking the time to report an issue.
What's wrong... Please write below.
I have an Excel template that has a series of blank rows
row 2 headings
rows 3 to 30 blank rows
row 34 formulas to sum values in columns =Sum(B4:B33)
Repeated about 5 times further down the worksheet
Access opens the workbook and uses CopyToRecordset to populate Excel from row 4 downwards. Will never be more than 30 rows.
But what I want to do is when the copy has taken place I want to remove blank rows when a row in column B is blank.
What I did was something like
For X = 4 to 250
If Range("B" & X).Value = "" Then
delete the row
End If Next
But could not get it to work
Now the file needs to go to Excel where it is entered into a worksheet with a certain heading.
Problem is when opening the .xls converted file in Excel, no macros show up. If I open a blank worksheet in Excel my macro shows up. I lowered the security settings to the most basic level in Excel.
How do I make the macro show up in the file opened in Excel through the Access VBA, so I can run it?
objXLApp.Visible = True
I want to open the file with the second worksheet showing. So how can I have the file open, but show the second worksheet
I have an excel file that includes files that are ready to ship out. My access database already has those file names, but does not have the shipping box number. Is there any way to importthe excel file into my database, have it find those specific file names and update the shipping box number field? The excel file won't be adding any new records,
I need the data from the 3 Access tables to be populated across to the three tables as shown in the MS Excel file.
Assume the file path of the excel file is: C:\Documents and Settings\All Users\Documents\microsoft\Sample_File.xls
so the table Sample_Data_1 gets populated across to MS Excel worksheet Records_1 C11:F14 and so on.
In the original file I have a sub form which runs the macro.
If it is blank then pull from the "REVISED" column. And, if the "REVISED" column is blank,
This is the formula I am currently using, however it is showing blank cells where it should be showing a date:
DUE DATE: IIf(Not (IsNull([ESD])),[ESD],IIf(Not (IsNull([REVISED])),[REVISED],[DUE]))
I'm thinking the blank cells are coming up because of incorrect formatting possibly on excel worksheet. I've tried changing the formatting to DATE on the excel worksheet cells, and as soon as I link it again with Access it shows the "REVISED" column as TEXT rather than DATE.
The data in column F will vary, but will be contiguous. So, at any given run of the code, column F could be populated from F1:F5 or at another time F1:F10 etc.
Once selected, I want to shade the range.
Any Access code automation help for this is appreciated.!