Modify Exel output via VBA 1

[git2dn]

I created an Excel file based on 3 queries and a report. However, the alignment of the fields in the Excel file is not correct. Fields shifted over 1 cell position when the report was migrated to an Excel spreadsheet.

Based on the following procedures, is there a way to change the cells in the Excel file as follows:
Fortuantely, the Excel output file will always be in the same cell positions each time the function runs to geneate the Excel spreadsheet.


Ideally I would like to perform the following cell manipulations:

a) Set cells A1, C1 and D1 to blanks.

b) move value of cell G9 TO F9 (OVERLAY F9)
I9 TO H9 (OVERLAY H9)

G17 TO F17 (OVERLAY F17)
I17 TO H17 (OVERLAY H17)

G25 TO F25 (OVERLAY F25)
I25 TO H25

G33 TO F33
I33 TO H33

G41 TO F41
I41 TO H41

G42 TO F42
I42 TO H42

Overlay the contents H25, F33, H33, F41, H41, F42 AND H42.



Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Private Function StartDoc(ByVal FileName As String, Optional CommandLine As String = "") As Long
StartDoc = ShellExecute(0&, "Open", FileName, CommandLine, vbNullString, 1)
End Function

Private Function isFileExist(filePath As String) As Boolean
isFileExist = (filePath <> "" And dir$(filePath) <> "")
End Function

Private Sub QuerySummToExcel()
Dim rstQuery As ADODB.Recordset, ExportedFile As String

ExportedFile = "C:\MyFileName.xls"

Set rstQuery = CurrentProject.Connection.Execute("qryCustValMthRange", , adCmdStoredProc)
Set rstQuery = CurrentProject.Connection.Execute("qryCustValDollRange", , adCmdStoredProc)
Set rstQuery = CurrentProject.Connection.Execute("qryCMR", , adCmdStoredProc)
DoCmd.OutputTo acOutputReport, "RptSummaryReport", acFormatXLS, ExportedFile

if isFileExist(ExportedFile) then StartDoc ExportedFile
End Sub

 

[SkipVought]

Hi,

I'd recommend leaving the source sheet untouched.

Create a new sheet and then turn on your macro recorder and record the transformation from one sheet to the other.

Post back if you need help polishing up your code.

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[git2dn]

Can a macro like the following be modified and converted into a function and used with the functions noted in my question above so that some cells in my spreadsheet will automatically be repositioned without the user having to run a macro ?

Sub MoveCells()
Dim x As Variant
Dim i As Long
Application.ScreenUpdating = False
Union([A1], [C11]).ClearContents
For Each x In Array(9, 17, 25, 33, 41, 42)
i = CLng(x)
Cells(i, 6) = Cells(i, 7)
Cells(i, 7).ClearContents
Cells(i, 8) = Cells(i, 9)
Cells(i, 9).ClearContents
Next
Application.ScreenUpdating = True
End Sub

 

[SkipVought]

A FUNCTION returns a value. If its JUST a VB function, it can change stuff on a sheet.

HOWEVER, if it is used as a SPREADSHEET function, Excel will not allow it to change any other cell other than the cell receiving the function value (the cell where the function resides)

BOTTOM LINE: Some event must execute the procedure.

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[xlbo]

To answer partially, yes, a recorded macro CAN be used as a function. To fully answer, NO your macro cannot. This is nothing to do with you - more what a function can or cannot do.

A function in a worksheet cannot change any cell other than the one it resides in so you cannot use a custom function within a worksheet to perform operations on the worksheet. If you are worried about users forgetting to run the mnacro, you can attach it to "events" so that it can fire automatically...

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[xlbo]

must've been mere seconds in that one Skip !!

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Did I hear an echo...echo..._echo...[white]_echo[/white]

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[xlbo]

LOL - always amuses me to think of us sat at opposite (near enough) sides of the globe, hitting the submit button to the same question within seconds of each other !!

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

It IS amazing just to think that people around the world can communicate this way!!!

Some day we may enjoy a pint together -- a DEFINITE maybe!


Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[xlbo]

Skip - if you are in the UK or if I get over to Texas (? I think that's where you are) I will surely be up for a pint !!

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question