Excel makro

[iuet]

Hei,

Trenger noen tips / råd for å lage en makro som henter et fast område (D6:E19) en gang hver dag i excel filer som har filnavn etter dato, som ligger under samme katalog. Disse kolonnene skal endres fra kolonner til rader til en annen excel fil (Main regnearket) etter dato / neste ledige rad.

 

Noen forslag og tips?

 

Tor

 

[Harald Staff]

Hei Tor

 

Her var det mye. Første bud, brekk ned en sammensatt oppgave til oversiktlige deloppgaver og løs en og en bit.

 

Jeg skal prøve å svare på noe.

 

Hvis noe skal kjøre en gang pr dag må du spesifisere tidspunkt, og Excel må være påslått alle disse tidspunktene. Også helge- og høytidsdager? Også ved nettverksbrudd og Adobeoppdateringer og whatnot? Eller klarer du deg med en med bevisst operasjon a la "hent denne ukens data"?

 

Dato er minst førti forskjellige ting i en datamaskin, så du må sjekke formatet filene lagres under. I eksempelet tar jeg utgangspunkt i at i dag er 09092016

 

Den enkleste måten å hente data ut av en lukket Excelfil og inn i en annen åpen, er at makroen setter inn en formel til den lukkede filen, regner, og erstatter formelen med konstantverdier. Det er her du også organiserer hva du vil ha hvor, ikke lag kolonner-til-rader som en egen prosedyre. Eksempelmakro:

Sub Hent()
ActiveSheet.Range("A3").Formula = "='C:\Temp\[" & Format(Date, "ddmmyyyy") & ".xlsm]Damer'!$A$1"
ActiveSheet.Range("B3").Formula = "='C:\Temp\[" & Format(Date, "ddmmyyyy") & ".xlsm]Damer'!$A$2"
ActiveSheet.Calculate
DoEvents
ActiveSheet.Range("A3").Value = ActiveSheet.Range("A3").Value
ActiveSheet.Range("B3").Value = ActiveSheet.Range("B3").Value
End Sub

Til slutt, hva er neste ledige rad? Er det første hull i lista, eller raden under nederste brukte rad? I siste tilfelle:

Sub Test()
MsgBox LedigRad
End Sub

Function LedigRad() As Long
LedigRad = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row + 1
End Function

Beste hilsen Harald

[iuet]

hei

 

Tusen takk for svar. Jeg får en feilmelding i makroen. Er dessverre ikke så god på dette. 

Ser du noen åpenbare feil i vedlagt bildefil.

 

mvh

 

Tor

 

 

 

 

[iuet]

Hei Tor

 

Her var det mye. Første bud, brekk ned en sammensatt oppgave til oversiktlige deloppgaver og løs en og en bit.

 

Jeg skal prøve å svare på noe.

 

Hvis noe skal kjøre en gang pr dag må du spesifisere tidspunkt, og Excel må være påslått alle disse tidspunktene. Også helge- og høytidsdager? Også ved nettverksbrudd og Adobeoppdateringer og whatnot? Eller klarer du deg med en med bevisst operasjon a la "hent denne ukens data"?

 

Dato er minst førti forskjellige ting i en datamaskin, så du må sjekke formatet filene lagres under. I eksempelet tar jeg utgangspunkt i at i dag er 09092016

 

Den enkleste måten å hente data ut av en lukket Excelfil og inn i en annen åpen, er at makroen setter inn en formel til den lukkede filen, regner, og erstatter formelen med konstantverdier. Det er her du også organiserer hva du vil ha hvor, ikke lag kolonner-til-rader som en egen prosedyre. Eksempelmakro:

Sub Hent()
ActiveSheet.Range("A3").Formula = "='C:\Temp\[" & Format(Date, "ddmmyyyy") & ".xlsm]Damer'!$A$1"
ActiveSheet.Range("B3").Formula = "='C:\Temp\[" & Format(Date, "ddmmyyyy") & ".xlsm]Damer'!$A$2"
ActiveSheet.Calculate
DoEvents
ActiveSheet.Range("A3").Value = ActiveSheet.Range("A3").Value
ActiveSheet.Range("B3").Value = ActiveSheet.Range("B3").Value
End Sub

Til slutt, hva er neste ledige rad? Er det første hull i lista, eller raden under nederste brukte rad? I siste tilfelle:

Sub Test()
MsgBox LedigRad
End Sub

Function LedigRad() As Long
LedigRad = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row + 1
End Function

Beste hilsen Harald

Hei Harald,

 

Tusen takk for et meget godt forslag og tilbakemelding. Jeg får en feilmelding i makroen.

Jeg er dessverre ikke så god på dette. 

Ser du noen åpenbare feil i vedlagt bildefil.

 

mvh

 

Tor

[Harald Staff]

Hei igjen Tor

 

Du vet, jeg jobber jo helt i blinde når jeg verken vet hva du kan eller hva du skal eller hvorfor. Men ja. Du har en blå markering på en apostrof i koden. Slett alt fra og med der og opp. En makro skal starte med

 

Sub Makronavn()

 

Og ende med

 

End Sub

 

Man kan ikke putte en Sub inni en annen, det er der feilen er.

 

På samme måte starter en funksjon med navn og datatype

 

Function Funksjonsnavn() as Long

 

Og ender med

 

End Function

[iuet]

Hei igjen Harald,

 

Jeg klarer å få makroen til å hente verdiene inn i nytt regneark. Men må likevel gjøre noen manuelle valg i makroen.

 

1. Når jeg kjører makroen kommer jeg til utforsker vinduet under riktig katalog,

Google Drive/NOREXECO_MAIN/Exch/Closing prices/  - Her må jeg velge fil med riktig dato/filnavn.

 

Disse filene heter 20160902_DAILY_SETTLEMENT_PRICES_NXE_FUT.xlsx - når jeg har "åpnet" riktig fil fra utforskeren får jeg deretter spørsmål om /dialog vindu om "VELG ARK" her velger jeg DSP arket (1 av 3 ark i excel fila) deretter trykker jeg "OK". Da fremkommer alle verdiene i regnearket.

 

Jeg tror det kan være noe rart med selve filnavnet som jeg har laget i makroen. Skulle ikke makroen kunne gå uten å åpne filen man skal hente data/verdier i fra. 

 

Sub Makro1()

 

ActiveSheet.Range("D6:D19").Formula = "='C:\Users\Tor\Google Drive\NOREXECO_MAIN\Exchange\Closing prices[" & Format(Date, "yyyymmdd_DAILY_SETTLEMENT_PRICES_NXE_FUT") & ".xlsx]Damer'!$D$6:$D$19"

ActiveSheet.Calculate

DoEvents

ActiveSheet.Range("D6:D19").Value = ActiveSheet.Range("D6:D19").Value

 

End Sub

 

mvh

 

Tor

[Harald Staff]

Hei igjen Tor

 

Bra. En ting om gangen som sagt. Trøbbelet her skyldes antakelig at du har lagt den faste teksten inn i datoformatteringskommandoen. Se om dette hjelper litt:

 

ActiveSheet.Range("D6:D19").Formula = "='C:\Users\Tor\Google Drive\NOREXECO_MAIN\Exchange\Closing prices[" & Format(Date, "yyyymmdd") & "_DAILY_SETTLEMENT_PRICES_NXE_FUT".xlsx]Damer'!$D$6:$D$19"

 

Så er jeg usikker på om et celleområde anerkjenner et annet celleområde som formel på denne måten. Mulig du må bruke FormulaR1C1 dersom det fortsatt feiler. Syntaksen får du da best ved å spille inn en makro mens du setter inn formelen manuelt. Men skal jeg gjette;

 

ActiveSheet.Range("D6:D19").FormulaR1C1 = "='C:\Users\Tor\Google Drive\NOREXECO_MAIN\Exchange\Closing prices[" & Format(Date, "yyyymmdd") & "_DAILY_SETTLEMENT_PRICES_NXE_FUT".xlsx]Damer'!RC"

 

Beste hilsen Harald

[iuet]

Hei igjen Tor

 

Bra. En ting om gangen som sagt. Trøbbelet her skyldes antakelig at du har lagt den faste teksten inn i datoformatteringskommandoen. Se om dette hjelper litt:

 

ActiveSheet.Range("D6:D19").Formula = "='C:\Users\Tor\Google Drive\NOREXECO_MAIN\Exchange\Closing prices[" & Format(Date, "yyyymmdd") & "_DAILY_SETTLEMENT_PRICES_NXE_FUT".xlsx]Damer'!$D$6:$D$19"

 

Så er jeg usikker på om et celleområde anerkjenner et annet celleområde som formel på denne måten. Mulig du må bruke FormulaR1C1 dersom det fortsatt feiler. Syntaksen får du da best ved å spille inn en makro mens du setter inn formelen manuelt. Men skal jeg gjette;

 

ActiveSheet.Range("D6:D19").FormulaR1C1 = "='C:\Users\Tor\Google Drive\NOREXECO_MAIN\Exchange\Closing prices[" & Format(Date, "yyyymmdd") & "_DAILY_SETTLEMENT_PRICES_NXE_FUT".xlsx]Damer'!RC"

 

Beste hilsen Harald

 

Hei Harald 

 

Tusen takk for input og gode forslag.

Jeg får en feilmelding når jeg skriver inn ditt forslag.

 

I "FormulaR1C1" får jeg feilmelding "Compile error: Expected end of statement".

 

Beklager at jeg tar opp din tid og jeg er helt klart en nybegynner i makro.  Hadde vært fint å få til denne "snurra".

 

mvh

 

Tor

[Harald Staff]

Jeg jobber litt i blinde uten dataene dine. Men det er et hermetegn etter FUT. Min feil, fjern det.

[iuet]

Jeg jobber litt i blinde uten dataene dine. Men det er et hermetegn etter FUT. Min feil, fjern det.

 

Hei, jeg kan e-maile deg noen eksempel filer og ev. noen illustrasjonsbilder.  Min e-post er [email protected].

 

mvh

 

Tor

[Harald Staff]

Hei igjen Tor. Jeg tror vi skal beholde diskusjonen i offentligheten. Mener ikke å være kjip, men både lesere og et søkbart arkiv mister noe ved epostassistanse.

 

Beste hilsen Harald

[iuet]

Hei igjen Tor. Jeg tror vi skal beholde diskusjonen i offentligheten. Mener ikke å være kjip, men både lesere og et søkbart arkiv mister noe ved epostassistanse.

 

Beste hilsen Harald

Hei Harald,

Det går helt fint.  Får samme feilen under "FormulaR1C1" - Expected end of statement.

 

 

mvh

 

Tor

[Harald Staff]

Hvis Closing prices er en mappe så trenger den en backslash etter seg. Denne funker fint hos meg:

 

Range("D6:D19").FormulaR1C1 = "='C:\Temp\[" & Format(Date, "yyyymmdd") & "_DAILY_SETTLEMENT_PRICES_NXE_FUT.xlsx]Damer'!RC"

[iuet]

Hvis Closing prices er en mappe så trenger den en backslash etter seg. Denne funker fint hos meg:

 

Range("D6:D19").FormulaR1C1 = "='C:\Temp\[" & Format(Date, "yyyymmdd") & "_DAILY_SETTLEMENT_PRICES_NXE_FUT.xlsx]Damer'!RC"

Hei,

 

Takk for tilbakemelding.  Det hikker fremdeles litt her. Nå stopper makroen på punktum mellom NXE_FUT".xlsx  - Prøvde noen alternativer her uten hell. Ser du ev. hva som er feil ?

 

Tor

[Harald Staff]

Det har jeg svart på tidligere, det er kanskje og kanskje ikke hele feilen: "det er et hermetegn etter FUT. Min feil, fjern det."